Skip site navigation (1) Skip section navigation (2)

Program Syntax Help Needed

From: SIMON(at)hslc(dot)org
To: pgsql-novice(at)postgresql(dot)org
Cc: simon(at)hslc(dot)org
Subject: Program Syntax Help Needed
Date: 2010-04-27 19:07:42
Message-ID: 01NMG906IM7A007QXD@ES45.HSLC.ORG (view raw or flat)
Thread:
Lists: pgsql-novice
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


Responses

pgsql-novice by date

Next:From: Michael GlaesemannDate: 2010-04-27 19:21:50
Subject: Re: Program Syntax Help Needed
Previous:From: Alan SimonDate: 2010-04-27 15:51:44
Subject: Program Syntax Help Needed

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group