From: Thomas F. O'Connell <tfo@sitening.com>
To: Michael Schmidt <MichaelMSchmidt@msn.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Numerical variables in pqsql statements
Date: Mon, 29 Aug 2005 14:58:38 -0500
Message-ID: <F104769F-4833-4E10-892B-8EDCC0AE0E27@sitening.com> (raw)
In-Reply-To: <BAY101-DAV8EA3AA3EF2A17C6F7C9EEA3B60@phx.gbl>
References: <BAY101-DAV8EA3AA3EF2A17C6F7C9EEA3B60@phx.gbl>
Michael,
PL/pgSQL variable interpolation works similarly to that in other
popular programming languages. If you have a statement -- whether
it's PERFORM, SELECT INTO, or EXECUTE -- a variable will get
interpolated during parsing if not escaped in a string. Per the
documentation, dynamic values in dynamic queries require special
handling since they might themselves contain quotes:
http://www.postgresql.org/docs/8.0/static/plpgsql-
statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
But your temp table statement below is fine if you know that there
will never be quotes in refer_num.
And it would work similarly if you built it dynamically via string
concatenation as a query to be executed with quote_literal escaping
for refer_num.
Typically, you need to protect yourself against user input to a
function. If you're computing values in the function body that you
know to be safe or passing in safe values generated elsewhere in the
application, you're less likely to need to quote your variables
explicitly or to build queries dynamically.
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
Strategic Open Source: Open Your i™
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
> numerical variables in pqsql. It was my impression one had to use
> EXECUTE on a concatenated string including quote_literal() for the
> variable containing the value. This can be quite a bit of
> 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 = a.author_key)
> WHERE ra.ref_num = refer_num;
>
> where refer_num is integer. This worked (surprisingly, to me).
> So, what is the rule regarding variables? Would this query work if
> I concatenated a string together, including quote_literal
> (refer_num) and then EXECUTEd it?
>
> Thanks and sorry to be so stupid.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tfo@sitening.com, MichaelMSchmidt@msn.com
Subject: Re: Numerical variables in pqsql statements
In-Reply-To: <F104769F-4833-4E10-892B-8EDCC0AE0E27@sitening.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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