Re: Problem with ALIAS?

On Wed, Dec 22, 2004 at 12:54:57AM -0500, Keith Worthington wrote:

> IPADB=# SELECT inventory.commit_scanner_data('monthly', 'this_is_a_test');
> WARNING:  Error occurred while executing PL/pgSQL function commit_scanner_data
> WARNING:  line 9 at SQL statement
> ERROR:  parser: parse error at or near "$1" at character 150

PostgreSQL 8.0 gives more context:

ERROR:  syntax error at or near "$1" at character 150
QUERY:  SELECT  CAST(extract(year from min(scan_timestamp)) || extract(month from min(scan_timestamp)) || extract(day
frommin(scan_timestamp)) AS date) AS  $1  FROM inventory.tbl_scanner 
CONTEXT:  PL/pgSQL function "commit_scanner_data" line 9 at SQL statement
LINE 1: ...xtract(day from min(scan_timestamp)) AS date) AS  $1  FROM i...

The offending code is:

>       SELECT CAST(extract(year from min(scan_timestamp)) || extract(month from
> min(scan_timestamp)) || extract(day from min(scan_timestamp)) AS date) AS
> v_inventory_date
>         FROM inventory.tbl_scanner;

PL/pgSQL appears confused over a column alias with the same name
as a variable in the DECLARE section (v_inventory_date).

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/