DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=TpTe9SvcoSkHhCm9Zldg/r3Bk6XMCL4iBdiLlmNoSmc1fuSTt/5Yqxx0J8tyUVRRD32Dfi514f8P+NWhlBiAlV9JraZbj6Z7Nm1+FROLGTxo38IgUzClSloD5948hmpvqBhJA780KtAb+mleiYy9KoVoCTiLG3g8LHCbzCdgECc=
Message-ID: <c666e3db05092421106c6bab2b@mail.gmail.com>
Date: Sat, 24 Sep 2005 21:10:48 -0700
From: Cere Davis <ceremona@gmail.com>
Reply-To: Cere Davis <ceremona@gmail.com>
To: Chris Browne <cbbrowne@acm.org>
Subject: Re: stored procs in postgresql
Cc: pgsql-sql@postgresql.org
In-Reply-To: <60vf0qma14.fsf@dba2.int.libertyrms.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <op.sxmghwnc1qrx7c@localhost>
	<60vf0qma14.fsf@dba2.int.libertyrms.com>

thanks,

I didn't even know about the string concatination function for this.=20
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 !=3D ''null''::bytea AND
contains(geometryn(bound_table.the_geom, 1), $1) =3D 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=3D$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 :=3D 'select t.id from ' || $2 || ' t where t.id =3D ' || $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=3D$1
> --
> let name=3D"cbbrowne" and tld=3D"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