Обсуждение: Problem with ALIAS?
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
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/
> 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
"Keith Worthington" <keithw@narrowpathinc.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