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

Luiz,

PERFECT.
Litle more coding, but works very nice.

Star for you from me.

By the way where in pgAdmin can I see created types.

Ilija

________________________________
From: Luiz K. 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>
Sent: Monday, May 28, 2012 4:04 PM
Subject: Re: [NOVICE] 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