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 from min(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/