From: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | SELECT UNION into a text[] |
Date: | 2018-10-09 16:32:06 |
Message-ID: | CAADeyWhfGB8QAox+HFaFPBkmtk5Zx1FRVLOqvASS7=VuitpANA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Good evening,
I am trying to SELECT ARRAY_AGG into an array from 2 tables.
But unfortunately here is what I get in PostgreSQL 10.5:
SELECT ARRAY_AGG(hashed)
FROM words_nouns
WHERE added > TO_TIMESTAMP(1539100913)
UNION
SELECT ARRAY_AGG(hashed)
FROM words_verbs
WHERE added > TO_TIMESTAMP(1539100913);
array_agg
-----------------------------------------------------
{noun1,noun2,noun3}
{verb1,verb2}
And thus I can not assign it to the _added variable in my custom function:
CREATE OR REPLACE FUNCTION words_get_added(
in_visited integer,
OUT out_json jsonb
) RETURNS jsonb AS
$func$
DECLARE
_added text[];
BEGIN
-- create array with words added to dictionary since in_visited
timestamp
IF in_visited > 0 THEN
_added := (
SELECT ARRAY_AGG(hashed)
FROM words_nouns
WHERE added > TO_TIMESTAMP(in_visited)
UNION
SELECT ARRAY_AGG(hashed)
FROM words_verbs
WHERE added > TO_TIMESTAMP(in_visited)
);
IF CARDINALITY(_added) > 0 THEN
out_json := jsonb_build_object('added', _added);
END IF;
END IF;
END
And the assignment results in the error message:
www java[1082]: org.postgresql.util.PSQLException: ERROR: more than one
row returned by a subquery used as an expression| Where: SQL statement
"SELECT (| SELECT ARRAY_AGG(hashed) |
FROM words_nouns | WHERE added >
TO_TIMESTAMP(in_visited)| UNION|
SELECT ARRAY_AGG(hashed) | FROM words_verbs |
WHERE added > TO_TIMESTAMP(in_visited)|
)"|PL/pgSQL function words_get_added(integer) line 7 at assignment
Please help me to modify my SELECT UNION so that I get just 1 row as result:
{noun1,noun2,noun3,verb1,verb2}
Regards
Alex
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2018-10-09 16:39:25 | Re: SELECT UNION into a text[] |
Previous Message | magodo | 2018-10-09 11:44:40 | Re: pg9.6: no backup history file (*.backup) created on hot standby |