Back to: All Forums → Seven Databases in Seven Weeks → Error when trying to import add_event.sql in postgres 8.4
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