Skip site navigation (1) Skip section navigation (2)

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

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 (view raw or flat)
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

In response to

Responses

pgsql-novice by date

Next:From: Ilija VidoevskiDate: 2012-05-28 17:21:37
Subject: Re: return values(table) from stored function from MS visual foxpro
Previous:From: Victor Whiskey YankeeDate: 2012-05-26 22:16:53
Subject: Re: could not access file "$libdir/plpgsql": No such file or directory

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group