Thread: pl/pgsql Limits
Hi All, As it is known that any funtion, written in pl/pgsql, can only retrun one tuple. I am just wondering it were true as well for function written in C language. I need to write few function that will retrun mulitiple rows satsifying a certain set of conditions. Where I can get some examples. Tahnks in advance. Najm
Najm Hashmi wrote: > Hi All, > As it is known that any funtion, written in pl/pgsql, can only > retrun one tuple. I am just wondering it were true as well for function > written in C language. I need to write few function that will retrun > mulitiple rows satsifying a certain set of conditions. Where I can get > some examples. > Tahnks in advance. > Najm Since the language handler is written in C, if it would've been possible (or make sense WRT the capabilities of the parser/planner/executor) I had done it for PL/pgSQL during initial creation of the language. We plan to tackle the problem for v7.2. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Jan Wieck <janwieck@Yahoo.com> writes:
>> As it is known that any funtion, written in pl/pgsql, can only
>> retrun one tuple. I am just wondering it were true as well for function
>> written in C language. I need to write few function that will retrun
>> mulitiple rows satsifying a certain set of conditions.
> We plan to tackle the problem for v7.2.
It is possible for a C function to return a set (ie, multiple values
returned over successive calls) as of 7.1; it's even documented, see
src/backend/utils/fmgr/README. And you can return a tuple if you know
how (this part is not documented, but you can crib it from the
SQL-function support in backend/executor/functions.c).
The real problem is that the rest of the system doesn't let you *do*
anything very useful with either set-valued or tuple-valued functions.
This is what we need to address in future releases. Ideally I think
a function returning sets and/or tuples should be treated as a table
source, so that you'd write ... FROM function(args) AS alias, ...
regards, tom lane
Tom Lane wrote: > Jan Wieck <janwieck@Yahoo.com> writes: > >> As it is known that any funtion, written in pl/pgsql, can only > >> retrun one tuple. I am just wondering it were true as well for function > >> written in C language. I need to write few function that will retrun > >> mulitiple rows satsifying a certain set of conditions. > > > We plan to tackle the problem for v7.2. > > It is possible for a C function to return a set (ie, multiple values > returned over successive calls) as of 7.1; it's even documented, see > src/backend/utils/fmgr/README. And you can return a tuple if you know > how (this part is not documented, but you can crib it from the > SQL-function support in backend/executor/functions.c). > > The real problem is that the rest of the system doesn't let you *do* > anything very useful with either set-valued or tuple-valued functions. > This is what we need to address in future releases. Ideally I think > a function returning sets and/or tuples should be treated as a table > source, so that you'd write ... FROM function(args) AS alias, ... > > regards, tom lane How do you work around this? All I can think of is to call a the function and have it create a temporary table, then select from that table in the same transaction (to use the same connection). Does a temporary table created by a function continue to exist after the function finishes? I have a stored procedure in SQL Server that I have to create a temporary table then select from the table inside the same procedure. I'm not sure it would work after the SP is done... Ian
Tom Lane wrote: > Jan Wieck <janwieck@Yahoo.com> writes: > >> As it is known that any funtion, written in pl/pgsql, can only > >> retrun one tuple. I am just wondering it were true as well for function > >> written in C language. I need to write few function that will retrun > >> mulitiple rows satsifying a certain set of conditions. > > > We plan to tackle the problem for v7.2. > > It is possible for a C function to return a set (ie, multiple values > returned over successive calls) as of 7.1; it's even documented, see > src/backend/utils/fmgr/README. And you can return a tuple if you know > how (this part is not documented, but you can crib it from the > SQL-function support in backend/executor/functions.c). > > The real problem is that the rest of the system doesn't let you *do* > anything very useful with either set-valued or tuple-valued functions. > This is what we need to address in future releases. Ideally I think > a function returning sets and/or tuples should be treated as a table > source, so that you'd write ... FROM function(args) AS alias, ... > > regards, tom lane OK, here's my brute force stupid hack to work around it. I create a temporary table, call my procedure, which inserts records into my temporary table, then I select from it. The table had to exist when I created the procedure, but that's OK, I just delete it afterwards. When I disconnect with psql, the temp table goes away. Of course I could just drop it, but I am curious... With AOLServer, which uses connection pooling, might my connection stay open all day and other transactions have visibility of my temporary table? I don't quite get that part. Ian
Hi all: has anyone heard of XQL? XQL is xml sql i just was catching up on some email and noticed this nugget http://www.ibiblio.org/xql/ anyways there is a xml::xql perl module so perl could do it but the ability to spit it right out of pg would be neato
Ian,
> That works, but when do you delete the records?
I delete the records:
a) When the user runs the report a second time, with different
parameters.
b) After the user exits, as part of a DB-wide clean-up procedure
(Function) that dumps everything with the user's session key.
-Josh Berkus
--
______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete
informationtechnology josh@agliodbs.com and data management solutions (415) 436-9166 for law firms, small
businesses fax 436-0137 and non-profit organizations. pager 338-4078 San
Francisco
Hi all, I must apologise as it turns out the 'culprit' wasn't really pl/pgsql. The test box I was testing on is Mandrake Linux 7.2, which comes with PostgreSQL 7.0.2. Everything else has version 7.0.3 installed on it, and I naively assumed that 7.0.3 was installed on the test box. After installing the Postgres 7.0.3 rpms from the PostgreSQL site, pl/pgsql is working consistently again. It looks like the rpms for PostgreSQL supplied with Mandrake Linux 7.2 are broken, I guess they didn't run the supplied tests before packaging. :-( Regards and best wishes, Justin Clift Database Administrator Justin Clift wrote: > > Hi all, > > I'm having trouble with what MAY BE a bug in PL/PGSQL for PG 7.0.3 on > Linux (Mandrake Linux 7.2). > > It appears pl/pgsql is munging values. When I pass it a 'time' value, > the value is altered without my code touching it. This is evidenced by > the stripped down function below : > > CREATE FUNCTION which_block(time) > RETURNS time > AS 'DECLARE > > /* Given a time, this function works out the name of the correct field > in the reservations table for it > * Written by : Justin Clift > * Date : 1st February 2001 > * Version : 1.00 > */ > hours char(3); > minutes char(2); > result char(5); > tempres char(5); > curnow datetime; > > BEGIN > > RETURN $1; > END;' > LANGUAGE 'plpgsql'; > > foobar=# select which_block(time '12:40:00'); > which_block > ------------- > 12:39:00 > (1 row) > > foobar=# > > Having passed it the time value of '12:40:00', I am immediately > returning that value and it is no longer '12:40:00'. > > Being over 1 month into using PostgreSQL 7.0.3 for a particular project, > this is scaring me as I'm now doubting the reliability of things. > > Regards and best wishes, > > Justin Clift > Database Administrator
Hi all,
I'm having trouble with what MAY BE a bug in PL/PGSQL for PG 7.0.3 on
Linux (Mandrake Linux 7.2).
It appears pl/pgsql is munging values. When I pass it a 'time' value,
the value is altered without my code touching it. This is evidenced by
the stripped down function below :
CREATE FUNCTION which_block(time)
RETURNS time
AS 'DECLARE
/* Given a time, this function works out the name of the correct field
in the reservations table for it* Written by : Justin Clift* Date : 1st February 2001* Version : 1.00*/
hours char(3); minutes char(2); result char(5); tempres char(5); curnow datetime;
BEGIN
RETURN $1; END;'
LANGUAGE 'plpgsql';
foobar=# select which_block(time '12:40:00');which_block
-------------12:39:00
(1 row)
foobar=#
Having passed it the time value of '12:40:00', I am immediately
returning that value and it is no longer '12:40:00'.
Being over 1 month into using PostgreSQL 7.0.3 for a particular project,
this is scaring me as I'm now doubting the reliability of things.
Regards and best wishes,
Justin Clift
Database Administrator
Justin Clift <aa2@bigpond.net.au> writes: > I'm having trouble with what MAY BE a bug in PL/PGSQL for PG 7.0.3 on > Linux (Mandrake Linux 7.2). > [ bogus roundoff behavior in date/time display ] This is a known silliness in the Mandrake distribution: their default compiler flags include both -O3 and -ffast-math, a combination that the gcc people will tell you does not work. The main result we've heard about is bogus roundoff behavior. I believe our recent Mandrake RPMs have been built with compiler flags that actually work, but if you got the files from someplace else or compiled them yourself then you might be vulnerable to this error. Check the mail list archives for more info, eg http://www.postgresql.org/mhonarc/pgsql-hackers/2000-08/msg00193.html regards, tom lane
Has been removed from the LDP website. J -- -- <COMPANY>CommandPrompt - http://www.commandprompt.com </COMPANY> <PROJECT>OpenDocs, LLC. - http://www.opendocs.org </PROJECT> <PROJECT>LinuxPorts - http://www.linuxports.com </PROJECT> <WEBMASTER>LDP - http://www.linuxdoc.org </WEBMASTER> -- Instead of asking why a piece of software is using "1970s technology," start asking why software is ignoring 30 years of accumulated wisdom. --
On Tue, 06 Feb 2001 08:50, Poet/Joshua Drake wrote: > Has been removed from the LDP website. Good news indeed! Now what are we going to do with it? Can the original document's source be made available so that somebody can do the needed work without having to re-key. There is a _lot_ of very good information in there buried underneath the ... um ... -- Sincerely etc., NAME Christopher SawtellCELL PHONE 021 257 4451ICQ UIN 45863470EMAIL csawtell @ xtra . co . nzCNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz -->> Please refrain from using HTML or WORD attachments in e-mails to me <<--
Hello, The Postgres team from PGSQL, Inc. has agreed to provide us with a new version. J On Wed, 7 Feb 2001, Christopher Sawtell wrote: >On Tue, 06 Feb 2001 08:50, Poet/Joshua Drake wrote: >> Has been removed from the LDP website. > >Good news indeed! > >Now what are we going to do with it? > >Can the original document's source be made available so that >somebody can do the needed work without having to re-key. > >There is a _lot_ of very good information in there buried underneath the >... um ... > > > -- -- <COMPANY>CommandPrompt - http://www.commandprompt.com </COMPANY> <PROJECT>OpenDocs, LLC. - http://www.opendocs.org </PROJECT> <PROJECT>LinuxPorts - http://www.linuxports.com </PROJECT> <WEBMASTER>LDP - http://www.linuxdoc.org </WEBMASTER> -- Instead of asking why a piece of software is using "1970s technology," start asking why software is ignoring 30 years of accumulated wisdom. --
> > Has been removed from the LDP website. > >>Good news indeed! >> >>Now what are we going to do with it? >> >>Can the original document's source be made available so that >>somebody can do the needed work without having to re-key. >> >>There is a _lot_ of very good information in there buried underneath the >>... um ... > >If there's any help with doing a rewrite let me know if I could >help. Writing/instruction has turned out to be a strong point for me... Of course, this is when I'm not writing fast and loose :)
> Has been removed from the LDP website. >Good news indeed! > >Now what are we going to do with it? > >Can the original document's source be made available so that >somebody can do the needed work without having to re-key. > >There is a _lot_ of very good information in there buried underneath the >... um ... If there's any help with doing a rewrite let me know if I could help. Writing/instruction has turned out to be a strong point for me... Thomas
the LDP project doesn't allow for 'taking over' someone elses work, but
Thomas is working the sites maintainer on an appropriate solution for the
problem, as even though its removed, it will come back again *groan*
On Tue, 6 Feb 2001, Thomas Swan wrote:
> > Has been removed from the LDP website.
>
> >Good news indeed!
> >
> >Now what are we going to do with it?
> >
> >Can the original document's source be made available so that
> >somebody can do the needed work without having to re-key.
> >
> >There is a _lot_ of very good information in there buried underneath the
> >... um ...
>
> If there's any help with doing a rewrite let me know if I could
> help. Writing/instruction has turned out to be a strong point for me...
>
> Thomas
>
>
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org