Re: RETURNS TABLE function returns nothingness

Adrian Klaver <adrian.klaver@xxxxxxxxxxx> · Fri, 2 Sep 2016 10:40:46 -0700

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.stehule@xxxxxxxxx
<mailto:pavel.stehule@xxxxxxxxx>> wrote:

    2016-09-02 19:21 GMT+02:00 Alexander Farber
    <alexander.farber@xxxxxxxxx <mailto:alexander.farber@xxxxxxxxx>>:

         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.klaver@xxxxxxxxxxx

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