From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: RETURNS TABLE function returns nothingness |
Date: | 2016-09-02 17:40:46 |
Message-ID: | a6afd8ab-7be4-4814-de0a-a7ba3d02de1c@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2016-09-02 17:45:07 | Re: RETURNS TABLE function returns nothingness |
Previous Message | Alexander Farber | 2016-09-02 17:35:24 | Re: RETURNS TABLE function returns nothingness |