Re: RETURNS TABLE function returns nothingness

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@gmail.com
> <mailto:pavel.stehule@gmail.com>> wrote:
>
>
>
>     2016-09-02 19:21 GMT+02:00 Alexander Farber
>     <alexander.farber@gmail.com <mailto:alexander.farber@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.klaver@aklaver.com