Skip site navigation (1) Skip section navigation (2)

Re: Problem with ALIAS?

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Keith Worthington <keithw(at)narrowpathinc(dot)com>
Cc: PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Problem with ALIAS?
Date: 2004-12-22 07:07:03
Message-ID: 20041222070703.GA9890@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-novice
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/

In response to

Responses

pgsql-novice by date

Next:From: gustavo.oliveiraDate: 2004-12-22 12:34:50
Subject: Encoding problems
Previous:From: Michael FuhrDate: 2004-12-22 06:52:40
Subject: Re: extracting min date and grouping

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group