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 got
RETURNS TABLE(konto character, naziv character) AS
Ilija
From: Luiz K. Matsumura <luiz@planit.com.br>
To: Ilija Vidoevski <ilija.vidoevski@yahoo.com>
Cc: "pgsql-novice@postgresql.org" <pgsql-novice@postgresql.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 (
kontochar,
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