Syntax error when using function in postgres

Question

I've been trying to execute the following sql in postgres 9.6. But I get the error: syntax error at or near "row". Which would be on the line where CREATE AND REPLACE FUNCTION starts.

I've made a sqlfiddle too if you prefer that: http://sqlfiddle.com/#!17/48a30/1

CREATE TEMPORARY TABLE input (
    id serial, certified boolean
);

CREATE TEMPORARY TABLE tests_person (
  id serial, certified boolean
);

INSERT INTO input (id, certified) VALUES (DEFAULT, True), (DEFAULT, False), (DEFAULT, True), (DEFAULT, False), (DEFAULT, True), (DEFAULT, False);

CREATE OR REPLACE FUNCTION update_record(row input) RETURNS RECORD AS $$
    UPDATE "tests_person"
    SET certified=row.certified
    WHERE certified=row.certified
    RETURNING *
$$ LANGUAGE SQL;

What is your goal actually ?

Oto Shavadze
– Oto Shavadze

2017-08-29 20:37:17 +00:00
Commented Aug 29, 2017 at 20:37 — Oto Shavadze
– Oto Shavadze, Commented Aug 29, 2017 at 20:37
SQL SERVER! won't have to do that!

BobSki
– BobSki

2017-08-29 20:54:28 +00:00
Commented Aug 29, 2017 at 20:54 — BobSki
– BobSki, Commented Aug 29, 2017 at 20:54

Kamran · Accepted Answer · 2017-08-29 20:38:30Z

4

row is a reserved word and you cannot use it as your parameter name. Try this instead

CREATE OR REPLACE FUNCTION update_record(_input input) RETURNS RECORD AS 
$$
    UPDATE tests_person
    SET certified=_input.certified
    WHERE certified=_input.certified
    RETURNING *
$$ LANGUAGE SQL;

answered Aug 29, 2017 at 20:38

Kamran

8531 gold badge8 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jonathan Over a year ago

Thank you! Such a stupid mistake.. Now I know at least.

Collectives™ on Stack Overflow

Syntax error when using function in postgres

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related