Thread: RETURNS TABLE function returns nothingness
Good evening,
please help me to figure out, 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;BEGINout_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)
Alex
2016-09-02 19:21 GMT+02:00 Alexander Farber <alexander.farber@gmail.com>:
Good evening,please help me to figure out, 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;BEGINout_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":" 07f0254f5e55413dec7f32c8ef4ee5 d3","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.
Regards
Pavel
Alex
If I'd like to always return exactly 1 row -
why wouldn't just RETURN work?
(That's what I kept trying)
On Fri, Sep 2, 2016 at 7:27 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
2016-09-02 19:21 GMT+02:00 Alexander Farber <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;BEGINout_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":"07 f0254f5e55413dec7f32c8ef4ee5d3 ","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.
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
Alexander Farber <alexander.farber@gmail.com> writes: > If I'd like to always return exactly 1 row - > why wouldn't just RETURN work? Because RETURNS TABLE means it's RETURNS SETOF something, which means the number of rows it produces is equal to the number of RETURN NEXTs executed. RETURN, per se, has exactly zero impact on the number of rows produced; it just stops execution. I think you can say RETURNS RECORD with a few OUT parameters to get the effect you're looking for. regards, tom lane