Search Postgresql Archives

SELECT UNION into a text[]

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux