Как сохранить селект в переменную?

Question

[Владимир Сергеевич]

Всем мир!
И вопрос сразу таков:
В примере кода функции вы можете увидеть два `SELECT ... AS _query` и естественно так делать нельзя; Помогите правильно преобразовать агрумент функции `_query` для дальнейшей работы с ним в запросе;
В SQL полный ноль и трогаю его только в крайней необходимости, пробовал объявить переменные через `DECLARE` но постоянно на что-то ругается интерпретатор(если так можно сказать)
СУБД postgresql
Заранее большое спасибо!)

CREATE OR REPLACE FUNCTION public.search_movies(_query character varying)
 RETURNS TABLE(id bigint, _table character varying, nameru character varying, nameen character varying, image text)
 LANGUAGE sql
AS $function$
SELECT regexp_replace(_query, '\M', ':* ', 'gi') AS _query;
SELECT regexp_replace(_query, '\M\:\*(?=(\s+?|)\m)', ':* | ', 'gi') AS _query;
SELECT *
FROM (
       (
         SELECT
           id,
           'movie'               AS _table,
           nameru,
           nameen,
           CAST(posters AS TEXT) AS image
         FROM movies
         WHERE tsv @@ to_tsquery('ru', _query)
         LIMIT 10
       )
       UNION
       (
         SELECT
           id,
           'staff'                   AS _table,
           staff_name,
           staff_name_en,
           CAST(staff_photo AS TEXT) AS image
         FROM movie_staff
         WHERE tsv @@ to_tsquery('ru', _query)
         LIMIT 10
       )

     ) t
ORDER BY
  CASE
  WHEN _table = 'movie'
    THEN 1
  WHEN _table = 'staff'
    THEN 2
  ELSE 3
  END;
$function$

Answer 1

[Сергей Горностаев]

В SQL ноль, но взялись за хранимые процедуры? Смело!

Проблемы с объявлением переменных в блоке declare у вас потому, что вместо language sql стоит использовать language plpgsql. А после объявление переменной можно будет сделать

SELECT regexp_replace(_query, '\M', ':* ', 'gi') INTO some_var;

Comments

[Владимир Сергеевич]

Спасибо!) Изменение смена на plpgsql помогло
Ну не прям ноль ноль, до этого селекты и инсерты и.т.п приходилось делать, но функции, тригеры и.т.д еще не юзал
Только вот еще новая ошибочка появилась:

[2016-10-30 14:45:44] [42702] ERROR: column reference "id" is ambiguous
Подробности: It could refer to either a PL/pgSQL variable or a table column.
Где: PL/pgSQL function search_movies(character varying) line 8 at SQL statement

Насколько я понимаю это из-за того что regexp_replace возвращает не просто строчку? Или что-то в этом духе?
Вот так функция выглядет сейчас:

CREATE OR REPLACE FUNCTION public.search_movies(_query CHARACTER VARYING)
  RETURNS TABLE(id BIGINT, _table CHARACTER VARYING, nameru CHARACTER VARYING, nameen CHARACTER VARYING, image TEXT)
AS $function$
DECLARE
  _query_first VARCHAR(255);
  _query_second VARCHAR(255);
BEGIN
  SELECT regexp_replace(_query, '\M', ':* ', 'gi') INTO _query_first;
  SELECT regexp_replace(_query_first, '\M\:\*(?=(\s+?|)\m)', ':* | ', 'gi') INTO _query_second;
  SELECT *
  FROM (
         (
           SELECT
             id,
             'movie'               AS _table,
             nameru,
             nameen,
             CAST(posters AS TEXT) AS image
           FROM movies
           WHERE tsv @@ to_tsquery('ru', _query_second)
           LIMIT 10
         )
         UNION
         (
           SELECT
             id,
             'staff'                   AS _table,
             staff_name,
             staff_name_en,
             CAST(staff_photo AS TEXT) AS image
           FROM movie_staff
           WHERE tsv @@ to_tsquery('ru', _query_second)
           LIMIT 10
         )

       ) t
  ORDER BY
    CASE
    WHEN _table = 'movie'
      THEN 1
    WHEN _table = 'staff'
      THEN 2
    ELSE 3
    END;

END;
$function$
LANGUAGE plpgsql

[Сергей Горностаев]

Владимир Жданов: он пишет вам, что не понимает к чему относится id, к таблице или переменной. Это из-за того, что у вас есть поле id в возвращаемом значении. Есть несколько вариантов решения это проблемы. В вашем случае проще всего будет перед declare добавить директиву #variable_conflict use_column.