pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedstored procs in postgresql
4+ messages / 4 participants
[nested] [flat]
* stored procs in postgresql
@ 2005-09-24 19:30 ceremona@gmail.com
2005-09-24 21:27 ` Re: stored procs in postgresql Chris Browne <cbbrowne@acm.org>
0 siblings, 1 reply; 4+ messages in thread
From: ceremona@gmail.com @ 2005-09-24 19:30 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have been having some trouble with plsql stored procs in postgres in
that I can
make a table name a variable in the stored proc. Is there some special
way to make this happen that I am unaware of?
For example, I want to do something like:
stored_proc(integer,varchar)
SELECT table_name.id
FROM table_name $2
WHERE table_name.id=$1
but I get an error about the $2 argument being no good.
Does anyone know how I can deal with this?
Thanks,
Cere
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: stored procs in postgresql
2005-09-24 19:30 stored procs in postgresql ceremona@gmail.com
@ 2005-09-24 21:27 ` Chris Browne <cbbrowne@acm.org>
2005-09-25 04:10 ` Re: stored procs in postgresql Cere Davis <ceremona@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Chris Browne @ 2005-09-24 21:27 UTC (permalink / raw)
To: pgsql-sql
ceremona@gmail.com writes:
> I have been having some trouble with plsql stored procs in postgres in
> that I can
> make a table name a variable in the stored proc. Is there some
> special way to make this happen that I am unaware of?
>
> For example, I want to do something like:
>
> stored_proc(integer,varchar)
>
> SELECT table_name.id
> FROM table_name $2
> WHERE table_name.id=$1
>
> but I get an error about the $2 argument being no good.
>
> Does anyone know how I can deal with this?
To do this sort of thing, you need to build up the query as a string,
and EXECUTE it.
Thus...
query := 'select t.id from ' || $2 || ' t where t.id = ' || $1 || ';';
The other vital problem is that the select is in bad form. The actual
name of the table needs to come BEFORE the alias, not after.
The following would represent more nearly legitimate SQL...
SELECT table_name.id
FROM $2 table_name
WHERE table_name.id=$1
--
let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/spiritual.html
And me, with this terrible pain in all the diodes down my left side...
-- Marvin the Paranoid Android
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: stored procs in postgresql
2005-09-24 19:30 stored procs in postgresql ceremona@gmail.com
2005-09-24 21:27 ` Re: stored procs in postgresql Chris Browne <cbbrowne@acm.org>
@ 2005-09-25 04:10 ` Cere Davis <ceremona@gmail.com>
2005-09-25 07:47 ` Re: stored procs in postgresql A. Kretschmer <akretschmer@despammed.com>
0 siblings, 1 reply; 4+ messages in thread
From: Cere Davis @ 2005-09-25 04:10 UTC (permalink / raw)
To: Chris Browne <cbbrowne@acm.org>; +Cc: pgsql-sql
thanks,
I didn't even know about the string concatination function for this.
Unfortunately, it was of no help. Specificly I am trying this, with
the following error:
SQL error:
ERROR: syntax error at or near "' || $2 || '" at character 110
In statement:
CREATE FUNCTION "getcensusbound" (geometry, character) RETURNS
character AS 'SELECT bound_table.name
FROM '' || $2 || '' bound_table
WHERE bound_table.the_geom::bytea != ''null''::bytea AND
contains(geometryn(bound_table.the_geom, 1), $1) = true;' LANGUAGE
"sql"
RETURNS NULL ON NULL INPUT
-Cere
On 9/24/05, Chris Browne <cbbrowne@acm.org> wrote:
> ceremona@gmail.com writes:
> > I have been having some trouble with plsql stored procs in postgres in
> > that I can
> > make a table name a variable in the stored proc. Is there some
> > special way to make this happen that I am unaware of?
> >
> > For example, I want to do something like:
> >
> > stored_proc(integer,varchar)
> >
> > SELECT table_name.id
> > FROM table_name $2
> > WHERE table_name.id=$1
> >
> > but I get an error about the $2 argument being no good.
> >
> > Does anyone know how I can deal with this?
>
> To do this sort of thing, you need to build up the query as a string,
> and EXECUTE it.
>
> Thus...
> query := 'select t.id from ' || $2 || ' t where t.id = ' || $1 || ';';
>
> The other vital problem is that the select is in bad form. The actual
> name of the table needs to come BEFORE the alias, not after.
>
> The following would represent more nearly legitimate SQL...
>
> SELECT table_name.id
> FROM $2 table_name
> WHERE table_name.id=$1
> --
> let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];;
> http://cbbrowne.com/info/spiritual.html
> And me, with this terrible pain in all the diodes down my left side...
> -- Marvin the Paranoid Android
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
Cere Davis
ceremona@gmail.com
-------------------
GPG Key: http://staff.washington.edu/cere/pubkey.asc
GPG fingerprint (ID# 73FCA9E6) : F5C7 627B ECBE C735 117B 2278 9A95
4C88 73FC A9E6
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: stored procs in postgresql
2005-09-24 19:30 stored procs in postgresql ceremona@gmail.com
2005-09-24 21:27 ` Re: stored procs in postgresql Chris Browne <cbbrowne@acm.org>
2005-09-25 04:10 ` Re: stored procs in postgresql Cere Davis <ceremona@gmail.com>
@ 2005-09-25 07:47 ` A. Kretschmer <akretschmer@despammed.com>
0 siblings, 0 replies; 4+ messages in thread
From: A. Kretschmer @ 2005-09-25 07:47 UTC (permalink / raw)
To: pgsql-sql
am 24.09.2005, um 21:10:48 -0700 mailte Cere Davis folgendes:
> thanks,
>
> I didn't even know about the string concatination function for this.
> Unfortunately, it was of no help. Specificly I am trying this, with
> the following error:
>
> SQL error:
>
>
> ERROR: syntax error at or near "' || $2 || '" at character 110
>
> In statement:
>
>
> CREATE FUNCTION "getcensusbound" (geometry, character) RETURNS
> character AS 'SELECT bound_table.name
1. you should better use dollar-quoting
Read:
http://www.postgresql.org/docs/8.0/static/plpgsql-development-tips.html
> FROM '' || $2 || '' bound_table
> WHERE bound_table.the_geom::bytea != ''null''::bytea AND
> contains(geometryn(bound_table.the_geom, 1), $1) = true;' LANGUAGE
> "sql"
2. you must build a string with your query and execute the string.
Read:
http://www.postgresql.org/docs/8.0/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
> On 9/24/05, Chris Browne <cbbrowne@acm.org> wrote:
> > ceremona@gmail.com writes:
3. please learn to quote.
Read: http://learn.to/quote
http://www.netmeister.org/news/learn2quote.html
Regards, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-09-25 07:47 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-24 19:30 stored procs in postgresql ceremona@gmail.com
2005-09-24 21:27 ` Chris Browne <cbbrowne@acm.org>
2005-09-25 04:10 ` Cere Davis <ceremona@gmail.com>
2005-09-25 07:47 ` A. Kretschmer <akretschmer@despammed.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