pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPL/PGSQL function with parameters
15+ messages / 8 participants
[nested] [flat]
* PL/PGSQL function with parameters
@ 2001-01-30 12:14 David Richter <D.Richter@DKFZ.de>
0 siblings, 2 replies; 15+ messages in thread
From: David Richter @ 2001-01-30 12:14 UTC (permalink / raw)
To: pgsql-sql
Folks,
I wrote that function, wich doesn't work. I want to hand over the name
of the tables(relation_table, update_table) and a
column(column_to_fill). The intention is, to use the function also with
other tables(not hard coded).
BUT this error appears :
psql:restructure.sql:32: ERROR: parser: parse error at or near "$1"
I didn't found any solution.
I would be grateful , if I could get some more Examples(more than in the
Docu of www.postgresql.org and Bruce Monjiam's Book) about parameters in
PL/PGSQL - functions.
I would be no less grateful if anybody give detailed suggestions.
CREATE FUNCTION patient_study_restructure (text,text,text) RETURNS
integer AS '
DECLARE
relation_table ALIAS FOR $1;
update_table ALIAS FOR $2;
column_to_fill ALIAS FOR $3;
psr_rec record;
bound integer;
i integer := 0;
BEGIN
FOR psr_rec IN SELECT * FROM relation_table LOOP
UPDATE update_table
SET column_to_fill = psr_rec.parentoid
WHERE chilioid = psr_rec.childoid;
i := i + 1;
END LOOP;
IF NOT FOUND THEN RETURN 1;
ELSE RETURN i;
END IF;
END;
' LANGUAGE 'plpgsql';
SELECT
patient_study_restructure('relpatient_study000','study','patientoid');
Anybody (Jan Wieck?) who can make some sugestions on
the above will
receive my enthusiastic gratitude.
David
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: PL/PGSQL function with parameters
@ 2001-02-05 16:00 Josh Berkus <josh@agliodbs.com>
parent: David Richter <D.Richter@DKFZ.de>
1 sibling, 0 replies; 15+ messages in thread
From: Josh Berkus @ 2001-02-05 16:00 UTC (permalink / raw)
To: David Richter <D.Richter@DKFZ.de>; +Cc: pgsql-sql
Mr. Richter,
> I wrote that function, wich doesn't work. I want to hand
> over the name
> of the tables(relation_table, update_table) and a
> column(column_to_fill). The intention is, to use the
> function also with
> other tables(not hard coded).
1. Try using type VARCHAR instead of TEXT for the
parameters. PL/pgSQL may be objecting to the "undefined"
size of TEXT.
2. I don't believe that you can supply variables in the
place of object names in PL/pgSQL, only in place of values.
Thus, "SELECT * FROM table_name" would be invalid.
(Jan, please tall me if I'm wrong in this; I could really
use the functionality if it *is* possible.)
You could use some creative manipulation of the system
tables to achieve the same result, however.
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: PL/PGSQL function with parameters
@ 2001-02-06 06:13 Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 15+ messages in thread
From: Josh Berkus @ 2001-02-06 06:13 UTC (permalink / raw)
To: pgsql-sql
Mike, Jan,
> Michael Ansley wrote:
>
> With the latest release, I think you can do:
>
> EXEC ''SELECT * FROM '' || $1;
>
> or
>
> DECLARE SQL VARCHAR;
> ...
> SQL = ''SELECT * FROM '' || $1;
> EXEC SQL;
>
> or something similar (it may be EXECUTE), which uses the dynamic sql
> elements of plpgsql. I think.
Is this true, Jan? Does anyone have more specific documentation?
-Josh Berkus
--
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: PL/PGSQL function with parameters
@ 2001-02-06 11:47 Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu>
0 siblings, 0 replies; 15+ messages in thread
From: Kovacs Zoltan @ 2001-02-06 11:47 UTC (permalink / raw)
To: D.Richter@DKFZ.de; +Cc: pgsql-sql
Unfortunately you cannot use parameters as you like. The FROM clause
cannot contain a parameter. It must be constant.
Zoltan
--
Kov\'acs, Zolt\'an
kovacsz@pc10.radnoti-szeged.sulinet.hu
http://www.math.u-szeged.hu/~kovzol
ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz
^ permalink raw reply [nested|flat] 15+ messages in thread
* RE: PL/PGSQL function with parameters
@ 2001-02-06 13:16 Michael Ansley <Michael.Ansley@intec-telecom-systems.com>
0 siblings, 2 replies; 15+ messages in thread
From: Michael Ansley @ 2001-02-06 13:16 UTC (permalink / raw)
To: 'David Richter' <D.Richter@DKFZ-heidelberg.de>; +Cc: 'Kovacs Zoltan' <kovacsz@pc10.radnoti-szeged.sulinet.hu>; 'Josh Berkus' <josh@agliodbs.com>; pgsql-sql
Just for the record:
DROP FUNCTION table_count(varchar);
CREATE FUNCTION table_count(varchar) RETURNS integer AS '
DECLARE
SQL varchar;
RES integer;
BEGIN
SQL = ''SELECT * INTO temp1 FROM '' || $1;
EXECUTE SQL;
SELECT count(*) INTO RES FROM temp1;
RETURN(RES)
END;
'
LANGUAGE 'plpgsql';
...
dev=> select table_count('switch');
test
------
6
(1 row)
This function produces exactly what you would hope for, a count of rows in
the specified table. It's particularly inefficient at doing it, because it
does a table copy (and doesn't bother to clean up after itself ;-(), so
don't do this on a large table ;-) but it shows the principle.
What I couldn't get it to do was to select directly into the variable RES.
Perhaps someone could enlighten me.
Cheers...
MikeA
-----Original Message-----
From: David Richter [mailto:D.Richter@DKFZ-heidelberg.de]
Sent: 06 February 2001 09:39
To: Michael Ansley
Subject: Re: [SQL] PL/PGSQL function with parameters
Hello!
Thanks a lot for Your answer!
But with my version 7.0.2. this suggestion doesn't work:
It appears: parser: parse error at or near "exec" or
parser: parse error at or near "execute"
And how should i design the update command in the suggested way e.g.?
EXEC ''UPDATE '' ||$1
''SET '' || $2 '' = psr_rec.parentoid
WHERE chilioid = psr_rec.childoid;''
Wich exact release I will need to use this feature?
Wich one are You using?
Greetings
David
**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
www.mimesweeper.com
**********************************************************************
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: [SQL] PL/PGSQL function with parameters
@ 2001-02-06 16:16 Tom Lane <tgl@sss.pgh.pa.us>
parent: Michael Ansley <Michael.Ansley@intec-telecom-systems.com>
1 sibling, 2 replies; 15+ messages in thread
From: Tom Lane @ 2001-02-06 16:16 UTC (permalink / raw)
To: Michael Ansley <Michael.Ansley@intec-telecom-systems.com>; +Cc: Jan Wieck <JanWieck@Yahoo.com>; pgsql-sql; pgsql-hackers@postgresql.org
Michael Ansley <Michael.Ansley@intec-telecom-systems.com> writes:
> CREATE FUNCTION table_count(varchar) RETURNS integer AS '
> DECLARE
> SQL varchar;
> RES integer;
> BEGIN
> SQL = ''SELECT * INTO temp1 FROM '' || $1;
> EXECUTE SQL;
> SELECT count(*) INTO RES FROM temp1;
> RETURN(RES)
> END;
> '
> LANGUAGE 'plpgsql';
> What I couldn't get it to do was to select directly into the variable RES.
I tried this, and it seems that "SELECT ... INTO foo" is not executed
correctly by EXECUTE --- the INTO is handled as an ordinary select-into-
table construct rather than plpgsql's select-into-variable.
While I have not looked closely, I seem to recall that plpgsql handles
INTO by stripping that clause out of the statement before it's passed to
the SQL engine. Evidently that's not happening in the EXECUTE case.
Jan, do you agree this is a bug? Is it reasonable to try to repair it
for 7.1? If we do not change the behavior of EXECUTE now, I fear it
will be too late --- some people will come to depend on the existing
behavior.
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* RE: [SQL] PL/PGSQL function with parameters
@ 2001-02-06 16:27 Michael Ansley <Michael.Ansley@intec-telecom-systems.com>
0 siblings, 0 replies; 15+ messages in thread
From: Michael Ansley @ 2001-02-06 16:27 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: Jan Wieck <JanWieck@Yahoo.com>; pgsql-sql; pgsql-hackers@postgresql.org
Yes, that was why I wrote it in the way that I did. The table is
effectively given a constant name, and the count is got from the table with
a known name. But of a kludge, but in 45sec, that was all I could come up
with ;-)
It would be VERY useful to see it fixed.
Cheers...
MikeA
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 06 February 2001 16:16
To: Michael Ansley
Cc: Jan Wieck; sqllist; pgsql-hackers@postgresql.org
Subject: Re: [SQL] PL/PGSQL function with parameters
Michael Ansley <Michael.Ansley@intec-telecom-systems.com> writes:
> CREATE FUNCTION table_count(varchar) RETURNS integer AS '
> DECLARE
> SQL varchar;
> RES integer;
> BEGIN
> SQL = ''SELECT * INTO temp1 FROM '' || $1;
> EXECUTE SQL;
> SELECT count(*) INTO RES FROM temp1;
> RETURN(RES)
> END;
> '
> LANGUAGE 'plpgsql';
> What I couldn't get it to do was to select directly into the variable RES.
I tried this, and it seems that "SELECT ... INTO foo" is not executed
correctly by EXECUTE --- the INTO is handled as an ordinary select-into-
table construct rather than plpgsql's select-into-variable.
While I have not looked closely, I seem to recall that plpgsql handles
INTO by stripping that clause out of the statement before it's passed to
the SQL engine. Evidently that's not happening in the EXECUTE case.
Jan, do you agree this is a bug? Is it reasonable to try to repair it
for 7.1? If we do not change the behavior of EXECUTE now, I fear it
will be too late --- some people will come to depend on the existing
behavior.
regards, tom lane
**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
www.mimesweeper.com
**********************************************************************
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: [SQL] PL/PGSQL function with parameters
@ 2001-02-06 17:45 Josh Berkus <josh@agliodbs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 15+ messages in thread
From: Josh Berkus @ 2001-02-06 17:45 UTC (permalink / raw)
To: pgsql-sql; +Cc: pgsql-hackers@postgresql.org
Tom, Jan, Michael,
> While I have not looked closely, I seem to recall that plpgsql handles
> INTO by stripping that clause out of the statement before it's passed to
> the SQL engine. Evidently that's not happening in the EXECUTE case.
>
> Jan, do you agree this is a bug? Is it reasonable to try to repair it
> for 7.1? If we do not change the behavior of EXECUTE now, I fear it
> will be too late --- some people will come to depend on the existing
> behavior.
If you think that's the best way. What we're really all wanting is a wy
in PL/pgSQL to pass a parameter as an object name. Doing it *without*
using EXECUTE would be even better than modifying EXECUTE to accomdate
SELECT ... INTO variable.
If we can write queries that address tables by OID, that would give us a
quick workaround ... get the OID from pg_class, then pass it to the
query as variables of type OID:
SELECT column1_oid, column2_oid FROM table_oid
WHERE column2_oid = variable1
ORDER BY column1_oid;
OF course, having PL/pgSQL do this automatically would be even better,
but I suspect would require a *lot* of extra programming by Jan.
And all of this should be influenced by whatever you guys are planning
to do about Stored Procedures.
-Josh Berkus
--
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: [SQL] PL/PGSQL function with parameters
@ 2001-02-06 21:15 Florent Guillaume <efgeor@noos.fr>
parent: Michael Ansley <Michael.Ansley@intec-telecom-systems.com>
1 sibling, 1 reply; 15+ messages in thread
From: Florent Guillaume @ 2001-02-06 21:15 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: pgsql-hackers@postgresql.org; pgman@candle.pha.pa.us
> > SQL = ''SELECT * INTO temp1 FROM '' || $1;
>
> I tried this, and it seems that "SELECT ... INTO foo" is not executed
> correctly by EXECUTE --- the INTO is handled as an ordinary select-into-
> table construct rather than plpgsql's select-into-variable.
>
> While I have not looked closely, I seem to recall that plpgsql handles
> INTO by stripping that clause out of the statement before it's passed to
> the SQL engine. Evidently that's not happening in the EXECUTE case.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Re: [SQL] PL/PGSQL function with parameters
@ 2001-02-07 00:43 Tom Lane <tgl@sss.pgh.pa.us>
parent: Florent Guillaume <efgeor@noos.fr>
0 siblings, 0 replies; 15+ messages in thread
From: Tom Lane @ 2001-02-07 00:43 UTC (permalink / raw)
To: Florent Guillaume <efgeor@noos.fr>; +Cc: pgsql-hackers@postgresql.org; pgman@candle.pha.pa.us
Florent Guillaume <efgeor@noos.fr> writes:
> On this same subject, the plpgsql doc says to use
> SELECT expression INTO var FROM ...
> but Bruce's book, in several examples ("PL/PGSQL Functions" for instance,
> node203.html) uses
> SELECT INTO var expression FROM ...
> Both should work, but there may be there's something to straighten up here.
IIRC, the plpgsql code is actually *very* lax about where you put the
INTO; it'll suck it out from almost anyplace in the query string ...
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: PL/PGSQL function with parameters
@ 2001-02-07 07:52 Anatoly K. Lasareff <tolik@aaanet.ru>
parent: David Richter <D.Richter@DKFZ.de>
1 sibling, 0 replies; 15+ messages in thread
From: tolik@aaanet.ru @ 2001-02-07 07:52 UTC (permalink / raw)
To: David Richter <d.richter@dkfz.de>; +Cc: pgsql-sql
>>>>> "DR" == David Richter <d.richter@dkfz.de> writes:
DR> Folks,
DR> I wrote that function, wich doesn't work. I want to hand over the name
DR> of the tables(relation_table, update_table) and a
DR> column(column_to_fill). The intention is, to use the function also with
DR> other tables(not hard coded).
DR> BUT this error appears :
DR> psql:restructure.sql:32: ERROR: parser: parse error at or near "$1"
DR> I didn't found any solution.
DR> I would be grateful , if I could get some more Examples(more than in the
DR> Docu of www.postgresql.org and Bruce Monjiam's Book) about parameters in
DR> PL/PGSQL - functions.
DR> I would be no less grateful if anybody give detailed suggestions.
DR> CREATE FUNCTION patient_study_restructure (text,text,text) RETURNS
DR> integer AS '
DR> DECLARE
DR> relation_table ALIAS FOR $1;
DR> update_table ALIAS FOR $2;
DR> column_to_fill ALIAS FOR $3;
DR> psr_rec record;
DR> bound integer;
DR> i integer := 0;
DR> BEGIN
DR> FOR psr_rec IN SELECT * FROM relation_table LOOP
DR> UPDATE update_table
DR> SET column_to_fill = psr_rec.parentoid
DR> WHERE chilioid = psr_rec.childoid;
DR> i := i + 1;
DR> END LOOP;
DR> IF NOT FOUND THEN RETURN 1;
DR> ELSE RETURN i;
DR> END IF;
DR> END;
DR> ' LANGUAGE 'plpgsql';
DR> SELECT
DR> patient_study_restructure('relpatient_study000','study','patientoid');
DR> Anybody (Jan Wieck?) who can make some sugestions on
DR> the above will
DR> receive my enthusiastic gratitude.
DR> David
You _cannot_ use parameters value as table or column name inside
plpgsql function. So your construct SELECT * FROM relation_table (and
others similar) is wrong. The same in other words: you cannot make
dynamic queries by plpgsql. BUT! You can use EXECUTE statement which
exists in 7.1. Here is some doc:
EXECUTE {query-string}
where query-string is a string of type TEXT containing the query to be executed.
Unlike all other queries in PL/pgSQL, a query run by an EXECUTE statement is not prepared
and saved just once during the life of the server. Instead, the query is prepared each time the
statement is run. The query-string can be dynamically created within the procedure to
perform actions on variable tables and fields.
The results from SELECT queries are discarded by EXECUTE unless SELECT INTO is used to
save the results into a table.
An example:
EXECUTE ''UPDATE tbl SET ''
|| quote_ident(fieldname)
|| '' = ''
|| quote_literal(newvalue)
|| '' WHERE ...'';
This example shows use of the functions quote_ident(TEXT) and
quote_literal(TEXT). Variables containing field and table identifiers should be passed to
function quote_ident(). Variables containing literal elements of the dynamic query string
should be passed to quote_literal(). Both take the appropriate steps to return the input
text enclosed in single or double quotes and with any embedded special characters intact.
--
Anatoly K. Lasareff Email: tolik@aaanet.ru
http://tolikus.hq.aaanet.ru:8080 Phone: (8632)-710071
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: [SQL] PL/PGSQL function with parameters
@ 2001-02-08 11:37 Jan Wieck <janwieck@Yahoo.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 15+ messages in thread
From: Jan Wieck @ 2001-02-08 11:37 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql; pgsql-hackers@postgreSQL.org
Josh Berkus wrote:
> Tom, Jan, Michael,
>
> > While I have not looked closely, I seem to recall that plpgsql handles
> > INTO by stripping that clause out of the statement before it's passed to
> > the SQL engine. Evidently that's not happening in the EXECUTE case.
> >
> > Jan, do you agree this is a bug? Is it reasonable to try to repair it
> > for 7.1? If we do not change the behavior of EXECUTE now, I fear it
> > will be too late --- some people will come to depend on the existing
> > behavior.
>
> If you think that's the best way. What we're really all wanting is a wy
> in PL/pgSQL to pass a parameter as an object name. Doing it *without*
> using EXECUTE would be even better than modifying EXECUTE to accomdate
> SELECT ... INTO variable.
>
> If we can write queries that address tables by OID, that would give us a
> quick workaround ... get the OID from pg_class, then pass it to the
> query as variables of type OID:
>
> SELECT column1_oid, column2_oid FROM table_oid
> WHERE column2_oid = variable1
> ORDER BY column1_oid;
>
> OF course, having PL/pgSQL do this automatically would be even better,
> but I suspect would require a *lot* of extra programming by Jan.
Couple of problems here:
1. The main parser, which is used in turn by the SPI stuff,
doesn't allow parameters passed in for object-
identifiers.
2. I'm not sure if *all* statements are really supported by
SPI_prepare() plus SPI_execp(). EXECUTE currently uses
SPI_exec() to directly invoke the querystring.
3. PL/pgSQL needs a clean way to identify statements that
shall not be cached. First things that come to mind are
- statements using temporary objects
- statements invoking utility commands (or generally
any DDL)
- statements having parameters for object-identifiers
If identified as such non-cacheable query, PL/pgSQL
doesn't use SPI_saveplan() but recreates a new plan every
time.
4. PL handlers in general should have a registering
mechanism for a callback function. On any schema change
(i.e. shared syscache invalidation) this function is
called, causing the PL handler to invalidate *ALL*
function bytecodes and cached plans. Keeping track of
things like "var table.att%TYPE" used in a function would
be a mess - so better throw away anything.
Yes, that's a *lot* to do.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: [HACKERS] Re: PL/PGSQL function with parameters
@ 2001-02-08 12:22 Jan Wieck <janwieck@Yahoo.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 15+ messages in thread
From: Jan Wieck @ 2001-02-08 12:22 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Michael Ansley <Michael.Ansley@intec-telecom-systems.com>; Jan Wieck <JanWieck@Yahoo.com>; pgsql-sql; pgsql-hackers@postgresql.orgg
Tom Lane wrote:
> Michael Ansley <Michael.Ansley@intec-telecom-systems.com> writes:
> > CREATE FUNCTION table_count(varchar) RETURNS integer AS '
> > DECLARE
> > SQL varchar;
> > RES integer;
> > BEGIN
> > SQL = ''SELECT * INTO temp1 FROM '' || $1;
> > EXECUTE SQL;
> > SELECT count(*) INTO RES FROM temp1;
> > RETURN(RES)
> > END;
> > '
> > LANGUAGE 'plpgsql';
>
> > What I couldn't get it to do was to select directly into the variable RES.
>
> I tried this, and it seems that "SELECT ... INTO foo" is not executed
> correctly by EXECUTE --- the INTO is handled as an ordinary select-into-
> table construct rather than plpgsql's select-into-variable.
>
> While I have not looked closely, I seem to recall that plpgsql handles
> INTO by stripping that clause out of the statement before it's passed to
> the SQL engine. Evidently that's not happening in the EXECUTE case.
>
> Jan, do you agree this is a bug? Is it reasonable to try to repair it
> for 7.1? If we do not change the behavior of EXECUTE now, I fear it
> will be too late --- some people will come to depend on the existing
> behavior.
EXECUTE simply takes the string expression and throws it into
SPI_exec() without parsing. Changing that for 7.1 is *not*
possible.
The above can be accomplished by
DECLARE
ROW record;
RES integer;
BEGIN
FOR ROW IN EXECUTE
''SELECT count(*) AS N FROM '' || $1
LOOP
RES := N;
END LOOP;
RETURN RES;
END;
Not as elegant as it should be, but at least possible.
There's much to be done for a future version of PL/pgSQL, but
better support for dynamic SQL needs alot of functionality
added to the main parser and the SPI manager in the first
place.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Re: [SQL] PL/PGSQL function with parameters
@ 2001-02-08 15:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jan Wieck <janwieck@Yahoo.com>
0 siblings, 1 reply; 15+ messages in thread
From: Tom Lane @ 2001-02-08 15:10 UTC (permalink / raw)
To: Jan Wieck <janwieck@Yahoo.com>; +Cc: pgsql-sql; pgsql-hackers@postgresql.org
> Josh Berkus wrote:
>> If you think that's the best way. What we're really all wanting is a wy
>> in PL/pgSQL to pass a parameter as an object name. Doing it *without*
>> using EXECUTE would be even better than modifying EXECUTE to accomdate
>> SELECT ... INTO variable.
>>
>> If we can write queries that address tables by OID, that would give us a
>> quick workaround ... get the OID from pg_class, then pass it to the
>> query as variables of type OID:
>>
>> SELECT column1_oid, column2_oid FROM table_oid
>> WHERE column2_oid = variable1
>> ORDER BY column1_oid;
This is completely pointless, AFAICS. If you don't know what table
is to be selected from, then you can't do *any* semantic checking or
planning in advance, so you might as well just do the entire processing
at runtime. That's exactly what EXECUTE does. I don't see any
functional advantage in an intermediate step between plpgsql's normal
behavior (caching of query plans) and EXECUTE. If it bought some
readability over constructing a query string for EXECUTE, then maybe,
but dealing in table and column OIDs is not my idea of a pleasant or
readable way to program ...
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: PL/PGSQL function with parameters
@ 2001-02-08 16:45 Josh Berkus <josh@agliodbs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 15+ messages in thread
From: Josh Berkus @ 2001-02-08 16:45 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jan Wieck <janwieck@Yahoo.com>; pgsql-sql
Tom, Jan,
> This is completely pointless, AFAICS. If you don't know what table
> is to be selected from, then you can't do *any* semantic checking or
> planning in advance, so you might as well just do the entire processing
> at runtime. That's exactly what EXECUTE does. I don't see any
> functional advantage in an intermediate step between plpgsql's normal
> behavior (caching of query plans) and EXECUTE. If it bought some
> readability over constructing a query string for EXECUTE, then maybe,
> but dealing in table and column OIDs is not my idea of a pleasant or
> readable way to program ...
Well, given that between you and Jan you have addressed dynamic
querying, it seems that there is no point in tinkering further. Always
great to find that a problem has already been solved.
If I wasn't up to my hairline in behind-schedule projects, I'd offer to
write this up for the User's Manual. Actually, consider that a
medium-term commitment ... before the end of the year, I'll write a much
longer PL/pgSQL chapter which Jan can review & correct. (I think I'm in
a postion to do so, as the current app uses a large assortment of
PL/pgSQL functions as pseudo-middleware).
-Josh Berkus
--
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2001-02-08 16:45 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-30 12:14 PL/PGSQL function with parameters David Richter <D.Richter@DKFZ.de>
2001-02-05 16:00 ` Josh Berkus <josh@agliodbs.com>
2001-02-07 07:52 ` Anatoly K. Lasareff <tolik@aaanet.ru>
2001-02-06 06:13 Re: PL/PGSQL function with parameters Josh Berkus <josh@agliodbs.com>
2001-02-06 11:47 Re: PL/PGSQL function with parameters Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu>
2001-02-06 13:16 RE: PL/PGSQL function with parameters Michael Ansley <Michael.Ansley@intec-telecom-systems.com>
2001-02-06 16:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-02-06 17:45 ` Josh Berkus <josh@agliodbs.com>
2001-02-08 11:37 ` Jan Wieck <janwieck@Yahoo.com>
2001-02-08 15:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-02-08 16:45 ` Josh Berkus <josh@agliodbs.com>
2001-02-08 12:22 ` Jan Wieck <janwieck@Yahoo.com>
2001-02-06 21:15 ` Florent Guillaume <efgeor@noos.fr>
2001-02-07 00:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-02-06 16:27 RE: [SQL] PL/PGSQL function with parameters Michael Ansley <Michael.Ansley@intec-telecom-systems.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