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

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

________________________________
From: Luiz K. Matsumura <luiz(at)planit(dot)com(dot)br>
To: Ilija Vidoevski <ilija(dot)vidoevski(at)yahoo(dot)com>
Cc: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Sent: Friday, May 25, 2012 10:48 PM
Subject: Re: [NOVICE] return values(table) from stored function from MS visual foxpro

Em 24/05/2012 07:48, Ilija Vidoevski escreveu:
I want to migrate from MS Sqlserver 2008 r2 express to Postgresql
>I use postgresql 9.1.3
>I create this stored function (on Postgres side)
>CREATE OR REPLACE FUNCTION public.a_getkonta_table (
>  mkontoa char,
>  mkontob char
>)
>RETURNS TABLE (
>  konto char,
>  naziv char
>) AS
>$body$
>begin
>    return query
>    SELECT
>    konta.konto,
>    konta.naziv
>    FROM konta
>    WHERE konta.konto between mkontoa and mkontob;
>end ;
>$body$
>LANGUAGE 'plpgsql'
>VOLATILE
>CALLED ON NULL INPUT
>SECURITY INVOKER
>COST 100 ROWS 1000;
>I call this function with this code (from Microsoft VisualFoxPro side)
>mkontoa = '000000000'
>mkontob = '099999999'
>If SQLExec(handle,"select * from a_getkonta_table(?mkontoa,?mkontob)",'temp101') < 0
>   Aerror(laError)
>   Messagebox(laError[1,2])
>   return
>ENDIF
>Returned result set contains correct row numbers but fields length is 254.
>Structure of table konta is
>Konto char(9)
>Naziv char(45)
> 
I think your problem is this part of your function definition:

.
.
.
RETURNS TABLE (  konto char,  naziv char ) AS ...
where need to be

RETURNS TABLE (  konto char(9),  naziv char(45) ) AS ...
to postgres generate a result with your expected types