pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: 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