Hello

I searched some notes about this topic. I didn't find any usefull sample. Lot of samples are about external stored procedures and others about using table expression like

create function foo(i1)
returns table (a1 int)
as
 return table(select a1 from tab)

isn't clear if table attributes are related to output variables, but nobody join it together.

SQL/PSM sample:
create function accounts_of (customer_name char(20))
    returns table (       account_number char(10),
                          branch_name char(15)
                          balance numeric(12,2))
return table
    (select account_number, branch_name, balance
     from account A
     where exists (
         select *
         from depositor D
         where D.customer_name = accounts_of.customer_name
                   and D.account_number = A.account_number ))


correct calling of it is:
select *
from table (accounts_of (‘Smith’))

next sample:
CREATE FUNCTION filmtyp (art CHAR(2))
 RETURNS TABLE (titel VARCHAR(75), jahr INTEGER)
 LANGUAGE SQL
 READS SQL DATA
 NO EXTERNAL ACTION
 DETERMINISTIC
 RETURN
    SELECT titel, jahr
    FROM film
    WHERE film.art = filmtyp.art


Table functions are named as parametrised views too. I don't thing using OUT variables is good idea, because you will have problems with colum's names, which is problem for plpgsql.

http://www.wiscorp.com/SQL2003Features.pdf
http://wwwdvs.informatik.uni-kl.de/courses/NEDM/SS2004/Vorlesungsunterlagen/NEDM.Chapter.03.User-defined_Routines_and_Object_Behavior.pdf

Regards
Pavel Stehule



From: Tom Lane <[EMAIL PROTECTED]>
To: "Pavel Stehule" <[EMAIL PROTECTED]>
CC: [EMAIL PROTECTED]
Subject: Re: [PATCHES] Table function support Date: Tue, 10 Apr 2007 18:17:14 -0400

"Pavel Stehule" <[EMAIL PROTECTED]> writes:
> this patch allows using SQL2003 syntax for set returning functions. It is
> based on using new type of argmode - PROARGMODE_TABLE.

I've been looking at this, and my feeling is that we should drop the
PROARGMODE_TABLE business and just define RETURNS TABLE(x int, y int)
as exactly equivalent to RETURNS SETOF RECORD with x and y treated as
OUT parameters.  There isn't any advantage to distinguishing the cases
that outweighs breaking client code that looks at pg_proc.proargmodes.
I don't believe that the SQL spec prevents us from exposing those
parameter names to PL functions, especially since none of our PLs are
in the standard at all.

                        regards, tom lane

_________________________________________________________________
Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

              http://www.postgresql.org/docs/faq

Reply via email to