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/

Thanks Michael. I understand why PL/pgSQL is confused. I was trying to set
the value of that variable for later use not assign a column alias. How do I
store the value returned by the query?

Kind Regards,
Keith

______________________________________________
99main Internet Services http://www.99main.com