CODEWITHSUNDEEP
sqlpostgresqlfunction
develup
develup

Reputation: 41

PostgreSQL 9.4 - syntax error at or near "IF" - inside function

I'm getting the ERROR: syntax error at or near "IF" error form the following sample code.

Note: I did replace the queries to ease complexity.

CREATE OR REPLACE FUNCTION get_return_dev(
"Title" character varying,
"ID" character varying)
RETURNS x_returntype AS $$

select item from table1

IF NOT found THEN
select item from table2
END IF;
$$

Any suggestion would help!

Upvotes: 0

Views: 2126

Answers (1)

Patrick
Patrick

Reputation: 32384

You seem to be confused about the language of the function. You structure the function as if it is a SQL language function (no BEGIN \ END statements), but you use elements of the PL/pgSQL language. Since you obviously need the additional features of PL/pgSQL (conditional branching), I would suggest that you start by learning how a PL/pgSQL function is structured.

A SELECT statement returning a single row inside of a PL/pgSQL function needs a target to store its result.

If you need to return a result from a function, you need a RETURN statement inside the function body.

CREATE OR REPLACE FUNCTION get_return_dev(
  "Title" character varying,
  "ID" character varying)
  RETURNS x_returntype AS $$
DECLARE
  item_var   same_type_as_item_column;
  return_var x_returntype;
BEGIN
  SELECT item INTO item_var FROM table1;

  IF NOT found THEN
    SELECT item INTO item_var FROM table2;
  END IF;

  -- Do your processing
  ...
  return_var := ...;

  RETURN return_var;
END
$$ LANGUAGE plpgsql;

Upvotes: 3

Related Questions