Lists: | pgsql-novice |
---|
From: | "Keith Worthington" <keithw(at)narrowpathinc(dot)com> |
---|---|
To: | "PostgreSQL Novice" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Problem with ALIAS? |
Date: | 2004-12-22 05:54:57 |
Message-ID: | 20041222055457.M70638@narrowpathinc.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
Hi All,
I am working on the function below. whenever I run it I get the following:
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
IPADB=#
I have kicked this thing around a half dozen times and can't seem to find my
mistake. What am I doing wrong?
CREATE OR REPLACE FUNCTION inventory.commit_scanner_data(TEXT, TEXT) RETURNS
INTEGER AS '
DECLARE
v_inventory_date DATE;
rcrd_item RECORD;
rcrd_scanner RECORD;
v_inventory_type ALIAS FOR $1;
v_note ALIAS FOR $2;
BEGIN
-- Retrieve the minimum date to use as the inventory date.
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;
-- Insert the detail information into the detail table.
INSERT INTO inventory.tbl_detail
( inventory_date,
inventory_type,
note )
VALUES ( v_inventory_date,
v_inventory_type,
v_note );
-- Loop through the item ids.
FOR rcrd_item IN SELECT inventory.tbl_scanner.item_id,
sum( inventory.tbl_scanner.quantity ) AS total
FROM inventory.tbl_scanner
GROUP BY inventory.tbl_scanner.item_id
ORDER BY inventory.tbl_scanner.item_id
LOOP
-- Insert the current record into the target table.
INSERT INTO inventory.tbl_data
( inventory_id,
item_id,
quantity )
VALUES ( currval(''inventory.tbl_detail_inventory_id_seq''::text),
rcrd_item.item_id,
rcrd_item.total );
-- Insert the original scanner data into the archive table.
FOR rcrd_scanner IN SELECT inventory.tbl_scanner.scan_timestamp,
inventory.tbl_scanner.item_id,
inventory.tbl_scanner.quantity,
inventory.tbl_scanner.employee_id,
inventory.tbl_scanner.void
FROM inventory.tbl_scanner
WHERE inventory.tbl_scanner.item_id =
rcrd_item.item_id
LOOP
-- Insert the current record into the target table.
INSERT INTO inventory.tbl_archive
( inventory_id,
scan_timestamp,
item_id,
quantity,
employee_id,
void )
VALUES (
currval(''inventory.tbl_detail_inventory_id_seq''::text),
rcrd_scanner.scan_timestamp,
rcrd_scanner.item_id,
rcrd_scanner.quantity,
rcrd_scanner.employee_id,
rcrd_scanner.void );
END LOOP;
-- The record has been processed. Remove it from the scanner table.
DELETE
FROM inventory.tbl_scanner
WHERE inventory.tbl_scanner.item_id = rcrd_item.item_id;
END LOOP;
RETURN 1;
END;
' LANGUAGE 'plpgsql';
Kind Regards,
Keith
______________________________________________
99main Internet Services http://www.99main.com
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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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/
From: | "Keith Worthington" <keithw(at)narrowpathinc(dot)com> |
---|---|
To: | Michael Fuhr <mike(at)fuhr(dot)org> |
Cc: | PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Problem with ALIAS? |
Date: | 2004-12-22 14:40:32 |
Message-ID: | 20041222144032.M7154@narrowpathinc.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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/
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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | KeithW(at)narrowpathinc(dot)com |
Cc: | Michael Fuhr <mike(at)fuhr(dot)org>, PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Problem with ALIAS? |
Date: | 2004-12-22 16:08:43 |
Message-ID: | 4223.1103731723@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
"Keith Worthington" <keithw(at)narrowpathinc(dot)com> writes:
> 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?
SELECT ... INTO, not SELECT ... AS
regards, tom lane