From: | "Luiz K(dot) Matsumura" <luiz(at)planit(dot)com(dot)br> |
---|---|
To: | Ilija Vidoevski <ilija(dot)vidoevski(at)yahoo(dot)com> |
Cc: | pgsql-novice <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: return values(table) from stored function from MS visual foxpro |
Date: | 2012-05-28 14:04:22 |
Message-ID: | 4FC385E6.90709@planit.com.br |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Em 26/05/2012 03:17, Ilija Vidoevski escreveu:
> Luiz,
>
> How can I chage to your code :
> RETURNS TABLE ( konto char(9), naziv char(45) )
> I try to execute sql script
>
> CREATE OR REPLACE FUNCTION a_getkonta_table1(IN mkontoa character, IN mkontob character)
> RETURNS TABLE(konto character (9), naziv character (45)) AS
> $BODY$
> begin
> return query
> SELECT
> konta.konto,
> konta.naziv
> FROM konta
> WHERE konta.konto between mkontoa and mkontob;
>
> end ;
> $BODY$
> LANGUAGE plpgsql
> VOLATILE
> COST 100
> ROWS 1000;
>
> but after that I got
> RETURNS TABLE(konto character, naziv character) AS
> Ilija
Hi Ilija, sorry for my fault
I don´t knowed this limitation of RETURN TABLE sintax, ( may be this
become a enhancement request ?)
I usually use RETURN SETOF record or RETURN SETOF <type> like this:
CREATE OR REPLACE FUNCTION a_getkonta_table(IN mkontoa character, IN mkontob character)
RETURNS SETOF record AS
$BODY$
begin
return query
SELECT
konta.konto,
konta.naziv
FROM konta
WHERE konta.konto between mkontoa and mkontob;
end ;
$BODY$
LANGUAGE plpgsql
VOLATILE
then in query we need to do something like this
select * from a_getkonta_table( ?mkontoa , ?mkontob ) AS ( konto
char(9), naziv char(45) );
With a defined type we can do :
CREATE TYPEa_getkonta_table_type AS ( konto char(9), naziv char(45) )
CREATE OR REPLACE FUNCTION a_getkonta_table(IN mkontoa character, IN mkontob character)
RETURNS SETOF a_getkonta_table_type AS
...
then we now can use the query without "AS (...)" clausule:
select * from a_getkonta_table( ?mkontoa , ?mkontob )
Hope this help.
Luiz K. Matsumura
From | Date | Subject | |
---|---|---|---|
Next Message | Ilija Vidoevski | 2012-05-28 17:21:37 | Re: return values(table) from stored function from MS visual foxpro |
Previous Message | Victor Whiskey Yankee | 2012-05-26 22:16:53 | Re: could not access file "$libdir/plpgsql": No such file or directory |