Обсуждение: 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