Re: Program Syntax Help Needed

On 04/27/2010 11:51 AM, Alan Simon wrote:
> 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.
> Any help would be appreciated. Thanks.
>
> Alan
> simon(at)hslc(dot)org <mailto:simon(at)hslc(dot)org>

From what I can see, your function has the following problems:

1. The language is SQL instead of PLPGSQL.
2. Semicolon after "BEGIN" is a syntax error

I don't have your table structure so I gave it a little try, like this:

mgogala=# CREATE OR REPLACE FUNCTION add_copy_notes() RETURNS
integer AS $$
declare
copynote record;
begin
null;
return(0);
end;
$$ language plpgsql;
CREATE FUNCTION
mgogala=#

As you can see, it works, no syntax errors encountered.

--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com
The Leader in integrated Media Intelligence Solutions