RETURNS TABLE function returns nothingness

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;
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.

Regards

Pavel

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:

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

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

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

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