Re: 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