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

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

From: Ilija Vidoevski <ilija(dot)vidoevski(at)yahoo(dot)com>
To: "Luiz K(dot) Matsumura" <luiz(at)planit(dot)com(dot)br>
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 17:21:37
Message-ID: 1338225697.98470.YahooMailNeo@web113106.mail.gq1.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-novice
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 

In response to

pgsql-novice by date

Next:From: Jonatan ReinersDate: 2012-05-30 13:21:42
Subject: ERROR: invalid input syntax for integer
Previous:From: Luiz K. MatsumuraDate: 2012-05-28 14:04:22
Subject: Re: return values(table) from stored function from MS visual foxpro

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