SELECT UNION into a text[]

Started by Alexander Farberover 7 years ago3 messagesgeneral
Jump to latest
#1Alexander Farber
alexander.farber@gmail.com

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

#2David G. Johnston
david.g.johnston@gmail.com
In reply to: Alexander Farber (#1)
Re: SELECT UNION into a text[]

On Tue, Oct 9, 2018 at 9:32 AM Alexander Farber <alexander.farber@gmail.com>
wrote:

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);

SELECT array_agg(words) FROM (
SELECT hashed FROM words_nouns
UNION ALL
SELECT hashed FROM words_verbs
) AS src

David J.

#3Andrew Gierth
andrew@tao11.riddles.org.uk
In reply to: David G. Johnston (#2)
Re: SELECT UNION into a text[]

"David" == David G Johnston <david.g.johnston@gmail.com> writes:

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);

David> SELECT array_agg(words) FROM (
David> SELECT hashed FROM words_nouns
David> UNION ALL
David> SELECT hashed FROM words_verbs
David> ) AS src

Or even better,

SELECT ARRAY(SELECT hashed FROM words_nouns
UNION
SELECT hashed FROM words_verbs);

--
Andrew (irc:RhodiumToad)