Thread: Program Syntax Help Needed
Hello -
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.
-- 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.
Alan
simon@hslc.orgAny help would be appreciated. Thanks.
Alan
Alan Simon <simon@hslc.org> writes: > -- 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 One fairly common gotcha is that you can't put a semicolon directly after BEGIN ... > psql:generate_notes.sql:26: ERROR: syntax error at or near "RECORD" > LINE 3: copy RECORD; > ^ ... although this error message suggests there's something else wrong in the immediate vicinity. But since what that shows doesn't match up with what you show above, I wonder whether you're confusing yourself by editing the wrong file. We've all done it :-( regards, tom lane
On Tuesday 27. April 2010 17.51.44 Alan Simon wrote: > JOIN staging_item_notes in > ON (b.id = in.bibkey) > WHERE in.bibkey != 0 You can't use "in" as an unquoted name, as that is a word with a special meaning in SQL. regards, -- Leif Biberg Kristensen http://solumslekt.org/blog/
On 04/27/2010 11:51 AM, Alan Simon wrote:
From what I can see, your function has the following problems:
Hello -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.simon@hslc.org
Any help would be appreciated. Thanks.
Alan
From what I can see, your function has the following problems:
- The language is SQL instead of PLPGSQL.
- Semicolon after "BEGIN" is a syntax error
mgogala=# CREATE OR REPLACE FUNCTION add_copy_notes() RETURNS integer AS $$As you can see, it works, no syntax errors encountered.
declare
copynote record;
begin
null;
return(0);
end;
$$ language plpgsql;
CREATE FUNCTION
mgogala=#
-- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 www.vmsinfo.com The Leader in integrated Media Intelligence Solutions