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

The result data type is not related to your existing table. You're using
the datatype char for both result columns.
That type is padded with spaces.
I would always advise to use varchar(n) or "text" unless you need the
result to be exacly some length, like 254.

However you seem to be using numbers, why don't you use the "integer" data
type?

http://www.postgresql.org/docs/9.1/static/datatype.html

HTH

WBL

On Thu, May 24, 2012 at 12:48 PM, Ilija Vidoevski <ilija(dot)vidoevski(at)yahoo(dot)com
> wrote:

> 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 use this connection string:
>
> Connstr="DRIVER={PostgreSQL odbc driver(unicode)};server=localhost;Port=5432;Database=mydatabase; Uid=postgres;Pwd=mypwd; UseServerSidePrepare=1;MaxVarcharSize=254;UnknownsAsLongVarchar=0;TextAsLongVarchar=0;"
>
> OS Window 7 profesional
>
> Any solutions ?
>

--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth