Re: return values(table) from stored function from MS visual foxpro

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