Re: [GENERAL] RETURNS TABLE function returns nothingness

[Adrian Klaver]

On 09/02/2016 10:35 AM, Alexander Farber wrote:
If I'd like to always return exactly 1 row -
why wouldn't just RETURN work?
(That's what I kept trying)

Because you are using RETURNS TABLE. Postgres(plpgsql) has no way of 
knowing what number of rows you are going to return.


On Fri, Sep 2, 2016 at 7:27 PM, Pavel Stehule <pavel.steh...@gmail.com
<mailto:pavel.steh...@gmail.com>> wrote:



    2016-09-02 19:21 GMT+02:00 Alexander Farber
    <alexander.far...@gmail.com <mailto:alexander.far...@gmail.com>>:

         why doesn't this simple test function return a row with 42,
        NULL values:

            CREATE OR REPLACE FUNCTION words_merge_users_2(
                            IN in_users jsonb,
                            IN in_ip inet
                    ) RETURNS TABLE (
                            out_uid integer,
                            out_banned varchar
                    ) AS
            $func$
            DECLARE
                    _user          jsonb;
                    _uids          integer[];
                    _created       timestamptz;
                    _vip           timestamptz;
                    _grand         timestamptz;
                    _banned_until  timestamptz;
                    _banned_reason varchar;
            BEGIN
                    out_uid := 42;
            END
            $func$ LANGUAGE plpgsql;


        Here I call it at PostgreSQL 9.5.4 prompt in MacOS:

            # select * from
            
words_merge_users_2('[{"given":"Abcde","social":1,"auth":"07f0254f5e55413dec7f32c8ef4ee5d3","stamp":1470237061,"female":0,"sid":"11111"}]
                              '::jsonb, '1.1.1.1'::inet);
             out_uid | out_banned
            ---------+------------
            (0 rows)


        Thank you (I am probably missing something very obvious)


    There is not RETURN NEXT statement - so output is zero rows.



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general