I'm trying to write a function for postgresql to do some string manipulation...

Question

The purpose of the function is to take a string and, if it contains parens, delete everything in the parens. Here's what I have:

CREATE FUNCTION clearmethodparams(IN qname text) RETURNS text AS
  $BODY$        
  IF position($o$($o$ in qname) = 0 THEN
    return qname;
  ELSE 
    return substring(qname from 0 for position($p$($p$ in qname)) || $c$)$c$;
  END IF;
  $BODY$
LANGUAGE sql VOLATILE;

The error it keeps giving me is

ERROR: syntax error at or near "IF" LINE 3: IF position($o$($o$ in qname) = 0 THEN

I've been trying to find some good documentation on function syntax, but nothing so far has been helpful.

Answer 1

You need to "convert" the function from an SQL function to a PL/pgSQL one - replace "LANGUAGE sql" with "LANGUAGE plpgsql" in your CREATE FUNCTION statement. Also you will need to "wrap" your code in a proper block.

OTOH you could probably achieve the same with an SQL function, e.g. with some clever user of CASE or similar.