Обсуждение: return values(table) from stored function from MS visual foxpro
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 ?
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
--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
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') < 0Aerror(laError)Messagebox(laError[1,2])returnENDIF
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 profesionalAny solutions ?
--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
Thank's WBL
I change table structure and input parametars, but the problem still exist.
When I sent SQL script from Foxpro side, result data types are OK. Strange!
It is not problem to fix this on Foxpro side, but many times thousands of rows are returned from server.
I want almost everything to do on server side.
I try within same function to create temp table on server side and alter fields structure, but nothig is changed.
Maybe this is ODBC problem ?
Ilija
From: Willy-Bas Loos <willybas@gmail.com>
To: Ilija Vidoevski <ilija.vidoevski@yahoo.com>
Cc: "pgsql-novice@postgresql.org" <pgsql-novice@postgresql.org>
Sent: Friday, May 25, 2012 10:41 AM
Subject: Re: [NOVICE] return values(table) from stored function from MS visual foxpro
To: Ilija Vidoevski <ilija.vidoevski@yahoo.com>
Cc: "pgsql-novice@postgresql.org" <pgsql-novice@postgresql.org>
Sent: Friday, May 25, 2012 10:41 AM
Subject: Re: [NOVICE] 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
--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
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') < 0Aerror(laError)Messagebox(laError[1,2])returnENDIF
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 profesionalAny solutions ?
--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
On Fri, May 25, 2012 at 12:49 PM, Ilija Vidoevski <ilija.vidoevski@yahoo.com> wrote:
-- I change table structure and input parametars, but the problem still exist.
What did you change it to? Integer?
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
To varchar.
I can not change this field to integer.
Other fields contain data like name, adress (street, number), city, country etc... and must be char or varchar.
I simply can not understand why when I retrive data from the stored function lenght of returnet fields based od char or varchar table field is 254 char.
With this code:
If SQLExec
(Handle,"select * from konta",'kontaq') < 0
Aerror(laError)
Messagebox(laError[1,2])
Endif
lenght of returned fields are OK.
Ilija
From: Willy-Bas Loos <willybas@gmail.com>
To: Ilija Vidoevski <ilija.vidoevski@yahoo.com>
Cc: "pgsql-novice@postgresql.org" <pgsql-novice@postgresql.org>
Sent: Friday, May 25, 2012 12:58 PM
Subject: Re: [NOVICE] return values(table) from stored function from MS visual foxpro
To: Ilija Vidoevski <ilija.vidoevski@yahoo.com>
Cc: "pgsql-novice@postgresql.org" <pgsql-novice@postgresql.org>
Sent: Friday, May 25, 2012 12:58 PM
Subject: Re: [NOVICE] return values(table) from stored function from MS visual foxpro
On Fri, May 25, 2012 at 12:49 PM, Ilija Vidoevski <ilija.vidoevski@yahoo.com> wrote:
-- I change table structure and input parametars, but the problem still exist.
What did you change it to? Integer?
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
wait, are you sure that you are running the right function?
the function is defined by it's input fields(incl types), so
a_getkonta_table(mkontoa character varying, mkontob character varying)
is a different function from
a_getkonta_table(mkontoa char, mkontob char)
so you would have 2 functions.
>Other fields contain data like name, adress ...
Other fields?? Oh, you mean you have a key-value approach?
i can't really say anything about odbc or foxpro
there is a seperate list for odbc by the way: http://archives.postgresql.org/pgsql-odbc/
cheers,
WBL
--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
the function is defined by it's input fields(incl types), so
a_getkonta_table(mkontoa character varying, mkontob character varying)
is a different function from
a_getkonta_table(mkontoa char, mkontob char)
so you would have 2 functions.
>Other fields contain data like name, adress ...
Other fields?? Oh, you mean you have a key-value approach?
i can't really say anything about odbc or foxpro
there is a seperate list for odbc by the way: http://archives.postgresql.org/pgsql-odbc/
cheers,
WBL
On Fri, May 25, 2012 at 2:48 PM, Ilija Vidoevski <ilija.vidoevski@yahoo.com> wrote:
To varchar.I can not change this field to integer.Other fields contain data like name, adress (street, number), city, country etc... and must be char or varchar.I simply can not understand why when I retrive data from the stored function lenght of returnet fields based od char or varchar table field is 254 char.With this code:If SQLExec(Handle,"select * from konta",'kontaq') < 0Aerror(laError)Messagebox(laError[1,2])Endiflenght of returned fields are OK.IlijaFrom: Willy-Bas Loos <willybas@gmail.com>Sent: Friday, May 25, 2012 12:58 PM
To: Ilija Vidoevski <ilija.vidoevski@yahoo.com>
Cc: "pgsql-novice@postgresql.org" <pgsql-novice@postgresql.org>
Subject: Re: [NOVICE] return values(table) from stored function from MS visual foxproOn Fri, May 25, 2012 at 12:49 PM, Ilija Vidoevski <ilija.vidoevski@yahoo.com> wrote:--I change table structure and input parametars, but the problem still exist.What did you change it to? Integer?
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
I apologize, I didn't see the beginning of this thread. If you have a problem with Visual FoxPro accessing remote datas, you can ask your question on the profox mailing list. Just look at www.leafe.com It's free and VERY efficient. Best regards -- Jean MAURICE Grenoble - France - Europe www.j-maurice.fr www.atoutfox.org www.aedtf.org
Thank's
I will try to find solution.
If I can not, I will have to stay on MS Sqlsrever 2008.
Maybe MS front-end app and MS back-end app is best choice.
Ilija
From: Willy-Bas Loos <willybas@gmail.com>
To: Ilija Vidoevski <ilija.vidoevski@yahoo.com>
Cc: "pgsql-novice@postgresql.org" <pgsql-novice@postgresql.org>
Sent: Friday, May 25, 2012 3:01 PM
Subject: Re: [NOVICE] return values(table) from stored function from MS visual foxpro
To: Ilija Vidoevski <ilija.vidoevski@yahoo.com>
Cc: "pgsql-novice@postgresql.org" <pgsql-novice@postgresql.org>
Sent: Friday, May 25, 2012 3:01 PM
Subject: Re: [NOVICE] return values(table) from stored function from MS visual foxpro
wait, are you sure that you are running the right function?
the function is defined by it's input fields(incl types), so
a_getkonta_table(mkontoa character varying, mkontob character varying)
is a different function from
a_getkonta_table(mkontoa char, mkontob char)
so you would have 2 functions.
>Other fields contain data like name, adress ...
Other fields?? Oh, you mean you have a key-value approach?
i can't really say anything about odbc or foxpro
there is a seperate list for odbc by the way: http://archives.postgresql.org/pgsql-odbc/
cheers,
WBL
--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
the function is defined by it's input fields(incl types), so
a_getkonta_table(mkontoa character varying, mkontob character varying)
is a different function from
a_getkonta_table(mkontoa char, mkontob char)
so you would have 2 functions.
>Other fields contain data like name, adress ...
Other fields?? Oh, you mean you have a key-value approach?
i can't really say anything about odbc or foxpro
there is a seperate list for odbc by the way: http://archives.postgresql.org/pgsql-odbc/
cheers,
WBL
On Fri, May 25, 2012 at 2:48 PM, Ilija Vidoevski <ilija.vidoevski@yahoo.com> wrote:
To varchar.I can not change this field to integer.Other fields contain data like name, adress (street, number), city, country etc... and must be char or varchar.I simply can not understand why when I retrive data from the stored function lenght of returnet fields based od char or varchar table field is 254 char.With this code:If SQLExec(Handle,"select * from konta",'kontaq') < 0Aerror(laError)Messagebox(laError[1,2])Endiflenght of returned fields are OK.IlijaFrom: Willy-Bas Loos <willybas@gmail.com>Sent: Friday, May 25, 2012 12:58 PM
To: Ilija Vidoevski <ilija.vidoevski@yahoo.com>
Cc: "pgsql-novice@postgresql.org" <pgsql-novice@postgresql.org>
Subject: Re: [NOVICE] return values(table) from stored function from MS visual foxproOn Fri, May 25, 2012 at 12:49 PM, Ilija Vidoevski <ilija.vidoevski@yahoo.com> wrote:--I change table structure and input parametars, but the problem still exist.What did you change it to? Integer?
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
On Fri, May 25, 2012 at 8:47 AM, Ilija Vidoevski <ilija.vidoevski@yahoo.com> wrote: > Thank's > > I will try to find solution. > If I can not, I will have to stay on MS Sqlsrever 2008. > Maybe MS front-end app and MS back-end app is best choice. > > Ilija > > From: Willy-Bas Loos <willybas@gmail.com> > To: Ilija Vidoevski <ilija.vidoevski@yahoo.com> > Cc: "pgsql-novice@postgresql.org" <pgsql-novice@postgresql.org> > Sent: Friday, May 25, 2012 3:01 PM > > Subject: Re: [NOVICE] return values(table) from stored function from MS > visual foxpro > > wait, are you sure that you are running the right function? > the function is defined by it's input fields(incl types), so > a_getkonta_table(mkontoa character varying, mkontob character varying) > is a different function from > a_getkonta_table(mkontoa char, mkontob char) > so you would have 2 functions. > >>Other fields contain data like name, adress ... > Other fields?? Oh, you mean you have a key-value approach? > > i can't really say anything about odbc or foxpro > there is a seperate list for odbc by the way: > http://archives.postgresql.org/pgsql-odbc/ > > cheers, > > WBL > > > On Fri, May 25, 2012 at 2:48 PM, Ilija Vidoevski <ilija.vidoevski@yahoo.com> > wrote: > > To varchar. > I can not change this field to integer. > > Other fields contain data like name, adress (street, number), city, country > etc... and must be char or varchar. > > I simply can not understand why when I retrive data from the stored function > lenght of returnet fields based od char or varchar table field is 254 char. > > With this code: > If SQLExec > > (Handle,"select * from konta",'kontaq') < 0 > Aerror(laError) > Messagebox(laError[1,2]) > Endif > > lenght of returned fields are OK. > > Ilija > From: Willy-Bas Loos <willybas@gmail.com> > To: Ilija Vidoevski <ilija.vidoevski@yahoo.com> > Cc: "pgsql-novice@postgresql.org" <pgsql-novice@postgresql.org> > Sent: Friday, May 25, 2012 12:58 PM > > Subject: Re: [NOVICE] return values(table) from stored function from MS > visual foxpro > > > > On Fri, May 25, 2012 at 12:49 PM, Ilija Vidoevski > <ilija.vidoevski@yahoo.com> wrote: > > I change table structure and input parametars, but the problem still exist. did you also change the output from the function? RETURNS TABLE ( konto char, naziv char ) should be RETURNS TABLE ( konto varchar, naziv varchar ) BTW, I've used foxpro front end + postgres backend before and I can tell you it works well. merlin
Em 24/05/2012 07:48, Ilija Vidoevski escreveu:
I think your problem is this part of your function definition:
to postgres generate a result with your expected types
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') < 0Aerror(laError)Messagebox(laError[1,2])returnENDIF
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 beRETURNS TABLE ( konto char(9), naziv char(45) ) AS ...
to postgres generate a result with your expected types
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 think your problem is this part of your function definition:
to postgres generate a result with your expected types
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') < 0Aerror(laError)Messagebox(laError[1,2])returnENDIF
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 beRETURNS TABLE ( konto char(9), naziv char(45) ) AS ...
to postgres generate a result with your expected types
Hi Merilin,
(1) This is code in the POSTGRESQL function
CREATE OR REPLACE FUNCTION public.a_getkonta_table (
mkontoa varchar,
mkontob varchar
)
RETURNS TABLE (
konto varchar,
naziv varchar
) 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;
mkontoa varchar,
mkontob varchar
)
RETURNS TABLE (
konto varchar,
naziv varchar
) 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;
(2) This is the code to "call" postgresql function on FOXPRO side:
If SQLExec(handle,"select * from a_getkonta_table(?mkontoa,?mkontob)",'temp101') < 0
Aerror(laError)
Messagebox(laError[1,2])
return
ENDIF
Aerror(laError)
Messagebox(laError[1,2])
return
ENDIF
(3) In the table konta field konto is varchar(6) and naziv is varchar(45)
In returned set lengh of filed konto and field naziv is 254.
I got the same result when I change the type of fields in table in char and also change code in function.
Ilija
From: Merlin Moncure <mmoncure@gmail.com>
To: Ilija Vidoevski <ilija.vidoevski@yahoo.com>
Cc: Willy-Bas Loos <willybas@gmail.com>; "pgsql-novice@postgresql.org" <pgsql-novice@postgresql.org>
Sent: Friday, May 25, 2012 8:32 PM
Subject: Re: [NOVICE] return values(table) from stored function from MS visual foxpro
On Fri, May 25, 2012 at 8:47 AM, Ilija Vidoevski
<ilija.vidoevski@yahoo.com> wrote:
> Thank's
>
> I will try to find solution.
> If I can not, I will have to stay on MS Sqlsrever 2008.
> Maybe MS front-end app and MS back-end app is best choice.
>
> Ilija
>
> From: Willy-Bas Loos <willybas@gmail.com>
> To: Ilija Vidoevski <ilija.vidoevski@yahoo.com>
> Cc: "pgsql-novice@postgresql.org" <pgsql-novice@postgresql.org>
> Sent: Friday, May 25, 2012 3:01 PM
>
> Subject: Re: [NOVICE] return values(table) from stored function from MS
> visual foxpro
>
> wait, are you sure that you are running the right function?
> the function is defined by it's input fields(incl types), so
> a_getkonta_table(mkontoa character varying, mkontob character varying)
> is a different function from
> a_getkonta_table(mkontoa char, mkontob char)
> so you would have 2 functions.
>
>>Other fields contain data like name, adress ...
> Other fields?? Oh, you mean you have a key-value approach?
>
> i can't really say anything about odbc or foxpro
> there is a seperate list for odbc by the way:
> http://archives.postgresql.org/pgsql-odbc/
>
> cheers,
>
> WBL
>
>
> On Fri, May 25, 2012 at 2:48 PM, Ilija Vidoevski <ilija.vidoevski@yahoo.com>
> wrote:
>
> To varchar.
> I can not change this field to integer.
>
> Other fields contain data like name, adress (street, number), city, country
> etc... and must be char or varchar.
>
> I simply can not understand why when I retrive data from the stored function
> lenght of returnet fields based od char or varchar table field is 254 char.
>
> With this code:
> If SQLExec
>
> (Handle,"select * from konta",'kontaq') < 0
> Aerror(laError)
> Messagebox(laError[1,2])
> Endif
>
> lenght of returned fields are OK.
>
> Ilija
> From: Willy-Bas Loos <willybas@gmail.com>
> To: Ilija Vidoevski <ilija.vidoevski@yahoo.com>
> Cc: "pgsql-novice@postgresql.org" <pgsql-novice@postgresql.org>
> Sent: Friday, May 25, 2012 12:58 PM
>
> Subject: Re: [NOVICE] return values(table) from stored function from MS
> visual foxpro
>
>
>
> On Fri, May 25, 2012 at 12:49 PM, Ilija Vidoevski
> <ilija.vidoevski@yahoo.com> wrote:
>
> I change table structure and input parametars, but the problem still exist.
did you also change the output from the function?
RETURNS TABLE (
konto
char,
naziv char
)
should be
RETURNS TABLE (
konto varchar,
naziv varchar
)
BTW, I've used foxpro front end + postgres backend before and I can
tell you it works well.
merlin
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
Em 26/05/2012 03:17, Ilija Vidoevski escreveu:
Hi Ilija, sorry for my faultLuiz,How can I chage to your code :RETURNS TABLE ( konto char(9), naziv char(45) )
I try to execute sql scriptCREATE 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 gotRETURNS TABLE(konto character, naziv character) AS Ilija
I don´t knowed this limitation of RETURN TABLE sintax, ( may be this become a enhancement request ?)
I usually use RETURN SETOF record or RETURN SETOF <type> like this:
CREATE OR REPLACE FUNCTION a_getkonta_table(IN mkontoa character, IN mkontob character) RETURNS SETOF record AS
$BODY$
begin return query SELECT konta.konto, konta.naziv FROM konta WHERE konta.konto between mkontoa and mkontob;
end ;
$BODY$ LANGUAGE plpgsqlVOLATILE
then in query we need to do something like this
select * from a_getkonta_table( ?mkontoa , ?mkontob ) AS ( konto char(9), naziv char(45) );
With a defined type we can do :
CREATE TYPE a_getkonta_table_type AS ( konto char(9), naziv char(45) )
CREATE OR REPLACE FUNCTION a_getkonta_table(IN mkontoa character, IN mkontob character) RETURNS SETOF a_getkonta_table_type AS ... then we now can use the query without "AS (...)" clausule: select * from a_getkonta_table( ?mkontoa , ?mkontob ) Hope this help. Luiz K. Matsumura
Luiz,
PERFECT.
Litle more coding, but works very nice.
Star for you from me.
By the way where in pgAdmin can I see created types.
Ilija
From: Luiz K. Matsumura <luiz@planit.com.br>
To: Ilija Vidoevski <ilija.vidoevski@yahoo.com>
Cc: pgsql-novice <pgsql-novice@postgresql.org>
Sent: Monday, May 28, 2012 4:04 PM
Subject: Re: [NOVICE] return values(table) from stored function from MS visual foxpro
Em 26/05/2012 03:17, Ilija Vidoevski escreveu:
Hi Ilija, sorry for my faultLuiz,How can I chage to your code :RETURNS TABLE ( konto char(9), naziv char(45) )
I try to execute sql scriptCREATE 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 gotRETURNS TABLE(konto character, naziv character) AS Ilija
I don´t knowed this limitation of RETURN TABLE sintax, ( may be this become a enhancement request ?)
I usually use RETURN SETOF record or RETURN SETOF <type> like this:
CREATE OR REPLACE FUNCTION a_getkonta_table(IN mkontoa character, IN mkontob character) RETURNS SETOF record AS
$BODY$
begin return query SELECT konta.konto, konta.naziv FROM konta WHERE konta.konto between mkontoa and mkontob;
end ;
$BODY$ LANGUAGE plpgsqlVOLATILE
then in query we need to do something like this
select * from a_getkonta_table( ?mkontoa , ?mkontob ) AS ( konto char(9), naziv char(45) );
With a defined type we can do :
CREATE TYPE a_getkonta_table_type AS ( konto char(9), naziv char(45) )
CREATE OR REPLACE FUNCTION a_getkonta_table(IN mkontoa character, IN mkontob character) RETURNS SETOF a_getkonta_table_type AS ... then we now can use the query without "AS (...)" clausule: select * from a_getkonta_table( ?mkontoa , ?mkontob ) Hope this help. Luiz K. Matsumura