Skip site navigation (1) Skip section navigation (2)

Re: proposal: table functions and plpgsql

From: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: proposal: table functions and plpgsql
Date: 2008-06-01 18:53:08
Message-ID: 162867790806011153j3f228e0bo79da6893595bc515@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Hello

After some days I thing, so idea of local types is wrong. Maybe we can
register output types for or SRF functions (maybe only for table
functions), but this mechanism is redundant to explicit custom types.
Local functions types are nice, they allows better compile time check,
but they are unnecessary.

Sample:
CREATE OR REPLACE FUNCTION foo(a integer)
RETURNS TABLE(a integer,  b integer) AS $$
DECLARE r record;
BEGIN
  FOR i IN 1..a LOOP
    r := ROW(i, i+1);
    RETURN NEXT r;
  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql;

or
-- more in SQL/PSM character
CREATE OR REPLACE FUNCTION foo(a integer)
RETURNS TABLE(a integer, b integer) AS $$
BEGIN
  RETURN TABLE SELECT i, i+1
                               FROM generate_series(1,a) g(i);
  RETURN;
END;
$$ LANGUAGE plpgsql;

any comments??

Regards
Pavel Stehule
2008/5/21 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
> Hello
>
> I am returning back to my patch and older proposal
> http://archives.postgresql.org/pgsql-hackers/2007-02/msg00318.php .
>
> Some work did Neil Conway
> http://archives.postgresql.org/pgsql-hackers/2007-07/msg00501.php and
> he commited half of this patch - RETURN QUERY part.
>
> Problematic part of my patch is implementation. Tom Lane proposal
> implenation RETURNS TABLE only as syntactic sugar for RETURNS SETOF
> RECORD. This is not comaptible with potential implementation, because
> it adds some default variables. My solution was special argmode, so I
> was able don't create default variables for output. My solution wasn't
> best too. It was ugly for current plpgsql where is often used RETURN
> NEXT statement (PSM doesn't know similar statement). I unlike default
> variables - it simply way to variables and column names collision.
>
> I propose following syntax for plpgsql:
>
> CREATE OR REPLACE FUNCTION foo(m integer)
> RETURNS TABLE (a integer, b integer) AS $$
> DECLARE r foo; -- same name as function, this type has local visibility
> BEGIN
>  FOR i IN 1..m LOOP
>    r.a := i; r.b := i + 1;
>    RETURN NEXT r;
>  END LOOP;
>  RETURN;
> END;
> $$ LANGUAGE plpgsql;
>
> In my proposal I don't create any default variables. Result type is
> only virtual - I don't need write it to system directory. I thing it's
> better than using some specific predeclared type as RESULTTYPE OR
> RESULTSET.
>
> What do you thing about?
>
> Regards
> Pavel Stehule
>

In response to

pgsql-hackers by date

Next:From: Pavel StehuleDate: 2008-06-01 18:59:48
Subject: Re: explain doesn't work with execute using
Previous:From: Dawid KuroczkoDate: 2008-06-01 18:31:13
Subject: Re: Core team statement on replication in PostgreSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group