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
