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!
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;
begin/``end` block as well as areturnstatement.