From: | Cere Davis <ceremona(at)gmail(dot)com> |
---|---|
To: | Chris Browne <cbbrowne(at)acm(dot)org> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: stored procs in postgresql |
Date: | 2005-09-25 04:10:48 |
Message-ID: | c666e3db05092421106c6bab2b@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)acm(dot)org> wrote:
> ceremona(at)gmail(dot)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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
--
Cere Davis
ceremona(at)gmail(dot)com
-------------------
GPG Key: http://staff.washington.edu/cere/pubkey.asc
GPG fingerprint (ID# 73FCA9E6) : F5C7 627B ECBE C735 117B 2278 9A95
4C88 73FC A9E6
From | Date | Subject | |
---|---|---|---|
Next Message | Ferindo Middleton Jr | 2005-09-25 04:46:52 | Re: redundancy in CHECK CONSTRAINTs |
Previous Message | Tom Lane | 2005-09-25 03:58:21 | Re: redundancy in CHECK CONSTRAINTs |