I am new to PostgreSQL and trying to create a program to update a table, but I am getting syntax errors.
Here is my program:
------------------------------------------------------------- -- Import copy_note data CREATE OR REPLACE FUNCTION add_copy_notes() RETURNS integer AS ' DECLARE copynote RECORD; BEGIN; FOR copynote IN SELECT * FROM asset.copy ac JOIN biblio.call_number cn ON (ac.call_number = cn.id) JOIN biblio.record_entry b ON (cn.record = b.id) JOIN staging_item_notes in ON (b.id = in.bibkey) WHERE in.bibkey != 0 LOOP INSERT INTO asset.copy_note (owning_copy, creator, create_date, pub, title, value) SELECT DISTINCT ac.id AS owning_copy, 1 AS creator, CAST 04-26-2010::DATE AS create_date, TRUE AS pub, ''Copy Note'' AS title, in.value AS value FROM staging_item_notes in; END LOOP; RETURN; END; ' LANGUAGE SQL; SELECT * FROM add_copy_notes(); -- COMMIT; -- ROLLBACK; -------------------------------------------------------------
and I get the following error messages:
psql:generate_notes.sql:26: ERROR: syntax error at or near "RECORD" LINE 3: copy RECORD; ^ psql:generate_notes.sql:27: ERROR: function add_copy_notes() does not exist LINE 1: SELECT * FROM add_copy_notes(); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.
I'm trying to retrieve one record at a time from the table asset.copy, which includes elements with different data types, in order to update asset.copy_note. Any help would be appreciated. Thanks.