Re: return values(table) from stored function from MS visual foxpro - Mailing list pgsql-novice

From Willy-Bas Loos
Subject Re: return values(table) from stored function from MS visual foxpro
Date
Msg-id CAHnozThcAJi-qt6UrbW4ynxvRN4AZTUEM6qD5mtCwubEp7YzTg@mail.gmail.com
Whole thread Raw
In response to return values(table) from stored function from MS visual foxpro  (Ilija Vidoevski <ilija.vidoevski@yahoo.com>)
Responses Re: return values(table) from stored function from MS visual foxpro
List pgsql-novice
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.vidoevski@yahoo.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 (
  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 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

pgsql-novice by date:

Previous
From: Ilija Vidoevski
Date:
Subject: return values(table) from stored function from MS visual foxpro
Next
From: Ilija Vidoevski
Date:
Subject: Re: return values(table) from stored function from MS visual foxpro