Postgres Pro
Downloads
Home   >   mailing lists

Thread: SELECT UNION into a text[]

SELECT UNION into a text[]

From
Alexander Farber
Date:
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

Re: SELECT UNION into a text[]

From
"David G. Johnston"
Date:
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.

Re: SELECT UNION into a text[]

From
Andrew Gierth
Date:
>>>>> "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)