pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: pl/pgsql Limits
6+ messages / 5 participants
[nested] [flat]

* Re: pl/pgsql Limits
@ 2001-01-13 14:04  Jan Wieck <janwieck@Yahoo.com>
  parent: Najm Hashmi <najm@mondo-live.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Jan Wieck @ 2001-01-13 14:04 UTC (permalink / raw)
  To: Najm Hashmi <najm@mondo-live.com>; +Cc: pgsql-sql; Tom Lane <tgl@sss.pgh.pa.us>

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 #





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: pl/pgsql Limits
@ 2001-01-13 17:31  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Jan Wieck <janwieck@Yahoo.com>
  0 siblings, 2 replies; 6+ messages in thread

From: Tom Lane @ 2001-01-13 17:31 UTC (permalink / raw)
  To: Jan Wieck <janwieck@Yahoo.com>; +Cc: Najm Hashmi <najm@mondo-live.com>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* pl/pgsql Limits
@ 2001-01-13 17:59  Najm Hashmi <najm@mondo-live.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Najm Hashmi @ 2001-01-13 17:59 UTC (permalink / raw)
  To: pgsql-sql; Tom Lane <tgl@sss.pgh.pa.us>

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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: pl/pgsql Limits
@ 2001-01-20 04:31  Ian Harding <iharding@pakrat.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 6+ messages in thread

From: Ian Harding @ 2001-01-20 04:31 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: pl/pgsql Limits
@ 2001-01-20 05:04  Ian Harding <iharding@pakrat.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 6+ messages in thread

From: Ian Harding @ 2001-01-20 05:04 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: pl/pgsql Limits
@ 2001-01-24 16:25  Josh Berkus <josh@agliodbs.com>
  parent: Ian Harding <iharding@pakrat.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Josh Berkus @ 2001-01-24 16:25 UTC (permalink / raw)
  To: Ian Harding <iharding@pakrat.com>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2001-01-24 16:25 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-13 17:59 pl/pgsql Limits Najm Hashmi <najm@mondo-live.com>
2001-01-13 14:04 ` Jan Wieck <janwieck@Yahoo.com>
2001-01-13 17:31   ` Tom Lane <tgl@sss.pgh.pa.us>
2001-01-20 04:31     ` Ian Harding <iharding@pakrat.com>
2001-01-20 05:04     ` Ian Harding <iharding@pakrat.com>
2001-01-24 16:25       ` Josh Berkus <josh@agliodbs.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox