SELECT COUNT(*) FROM gen_lenta WHERE lenta_id IN (SELECT id FROM lenta_bundle)
[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
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
И здесь уже становится понятнее и красивее; Так же замечу, что из-за отсутствия округления в формуле происходит ошибка при нечетном количестве элементов в массиве