Обсуждение: RETURNS TABLE function returns nothingness

Поиск
Список
Период
Сортировка

RETURNS TABLE function returns nothingness

От
Alexander Farber
Дата:
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)
Alex

Re: RETURNS TABLE function returns nothingness

От
Pavel Stehule
Дата:


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
 
Alex

Re: RETURNS TABLE function returns nothingness

От
Alexander Farber
Дата:
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;
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.

Re: RETURNS TABLE function returns nothingness

От
Adrian Klaver
Дата:
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

Re: RETURNS TABLE function returns nothingness

От
Tom Lane
Дата:
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