In-Reply-To: <BAY101-DAV8EA3AA3EF2A17C6F7C9EEA3B60@phx.gbl>
References: <BAY101-DAV8EA3AA3EF2A17C6F7C9EEA3B60@phx.gbl>
Mime-Version: 1.0 (Apple Message framework v734)
Content-Type: text/plain; charset=WINDOWS-1252; delsp=yes; format=flowed
Message-Id: <F104769F-4833-4E10-892B-8EDCC0AE0E27@sitening.com>
Cc: <pgsql-sql@postgresql.org>
Content-Transfer-Encoding: quoted-printable
From: "Thomas F. O'Connell" <tfo@sitening.com>
Subject: Re: Numerical variables in pqsql statements
Date: Mon, 29 Aug 2005 14:58:38 -0500
To: Michael Schmidt <MichaelMSchmidt@msn.com>

Michael,

PL/pgSQL variable interpolation works similarly to that in other =20
popular programming languages. If you have a statement -- whether =20
it's PERFORM, SELECT INTO, or EXECUTE -- a variable will get =20
interpolated during parsing if not escaped in a string. Per the =20
documentation, dynamic values in dynamic queries require special =20
handling since they might themselves contain quotes:

http://www.postgresql.org/docs/8.0/static/plpgsql-=20
statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

But your temp table statement below is fine if you know that there =20
will never be quotes in refer_num.

And it would work similarly if you built it dynamically via string =20
concatenation as a query to be executed with quote_literal escaping =20
for refer_num.

Typically, you need to protect yourself against user input to a =20
function. If you're computing values in the function body that you =20
know to be safe or passing in safe values generated elsewhere in the =20
application, you're less likely to need to quote your variables =20
explicitly or to build queries dynamically.

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Strategic Open Source: Open Your i=99

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)

On Aug 21, 2005, at 7:43 PM, Michael Schmidt wrote:

> Folks,
> I'm sure this is dumb, but I'm a little confused about use of =20
> numerical variables in pqsql.  It was my impression one had to use =20
> EXECUTE on a concatenated string including quote_literal() for the =20
> variable containing the value.  This can be quite a bit of =20
> trouble.  I just wrote a function that included the statement :
>
> CREATE TEMPORARY TABLE author_names AS
>       SELECT ra.ref_auth_key, a.last_name, a.first_name
>       FROM ref_auth ra INNER JOIN authors a
>       ON (ra.author_num =3D a.author_key)
>       WHERE ra.ref_num =3D refer_num;
>
> where refer_num is integer.  This worked (surprisingly, to me).  =20
> So, what is the rule regarding variables?  Would this query work if =20=

> I concatenated a string together, including quote_literal=20
> (refer_num) and then EXECUTEd it?
>
> Thanks and sorry to be so stupid.