12 Oct 2012, 05:47
Generic-user-small

Arun Rangarajan (2 posts)

When trying to import add_event.sql using ` book=# \i add_event.sql ` I got the following error: ` psql:add_event.sql:25: ERROR: syntax error at or near “$1” LINE 1: INSERT INTO events ( $1 , $2 , $3 , venue_id) VALUES ( $1 … ^ QUERY: INSERT INTO events ( $1 , $2 , $3 , venue_id) VALUES ( $1 , $2 , $3 , $4 ) CONTEXT: SQL statement in PL/PgSQL function “add_event” near line 18 `

The problem is that some variable names are the same as the column names. The following code works:

` CREATE OR REPLACE FUNCTION add_event( v_title text, v_starts timestamp, v_ends timestamp, venue text, postal varchar(9), country char(2) ) RETURNS boolean AS $$ DECLARE did_insert boolean := false; found_count integer; the_venue_id integer; BEGIN SELECT venue_id INTO the_venue_id FROM venues v WHERE v.postal_code=postal AND v.country_code=country AND v.name ILIKE venue LIMIT 1;

IF the_venue_id IS NULL THEN INSERT INTO venues (name, postal_code, country_code) VALUES (venue, postal, country) RETURNING venue_id INTO the_venue_id;

did_insert := true;   END IF; 

– Note: not an “error”, as in some programming languages RAISE NOTICE ‘Venue found %’, the_venue_id;

INSERT INTO events (title, starts, ends, venue_id) VALUES (v_title, v_starts, v_ends, the_venue_id);

RETURN did_insert; END; $$ LANGUAGE plpgsql; `

  You must be logged in to comment