Horton

Thread

  1. Re: pl/pgsql Limits

    Jan Wieck <janwieck@yahoo.com> — 2001-01-13T14:04:59Z

    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 #

  2. Re: pl/pgsql Limits

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-01-13T17:31:21Z

    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

  3. pl/pgsql Limits

    Najm Hashmi <najm@mondo-live.com> — 2001-01-13T17:59:09Z

    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

  4. Re: pl/pgsql Limits

    Ian Harding <iharding@pakrat.com> — 2001-01-20T04:31:09Z

    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

  5. Re: pl/pgsql Limits

    Ian Harding <iharding@pakrat.com> — 2001-01-20T05:04:12Z

    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

  6. [notion]: a possible language addition: XQL

    clayton cottingham <drfrog@smartt.com> — 2001-01-23T15:50:02Z

    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

  7. Re: pl/pgsql Limits

    Josh Berkus <josh@agliodbs.com> — 2001-01-24T16:25:58Z

    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 information technology      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

  8. HELP: Scarey pl/pgsql problem

    Justin Clift <aa2@bigpond.net.au> — 2001-02-01T02:50:55Z

    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

  9. Re: HELP: Scarey pl/pgsql problem

    Justin Clift <aa2@bigpond.net.au> — 2001-02-01T03:36:53Z

    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

  10. Re: HELP: Scarey pl/pgsql problem

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-02-05T19:35:53Z

    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

  11. Postgres-HOWTO

    Poet/Joshua Drake <poet@linuxports.com> — 2001-02-05T19:50:24Z

    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.
--

  12. Re: Postgres-HOWTO

    Christopher Sawtell <csawtell@xtra.co.nz> — 2001-02-06T23:10:45Z

    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 Sawtell
 CELL PHONE 021 257 4451
 ICQ UIN    45863470
 EMAIL      csawtell @ xtra . co . nz
 CNOTES     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 
<<--

  13. Re: Postgres-HOWTO

    Poet/Joshua Drake <poet@linuxports.com> — 2001-02-07T00:26:17Z

    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.
--

  14. Re: Postgres-HOWTO

    Thomas Swan <tswan-lst@ics.olemiss.edu> — 2001-02-07T00:43:33Z

     > 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

  15. Re: Postgres-HOWTO

    Thomas Swan <tswan-lst@ics.olemiss.edu> — 2001-02-07T00:44:40Z

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

  16. Re: Postgres-HOWTO

    Marc G. Fournier <scrappy@hub.org> — 2001-02-07T14:24:53Z

    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