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