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 plpgsqlVOLATILE COST 100 ROWS 1000;

but after that I gotRETURNS 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 plpgsqlVOLATILE

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 TYPE a_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