Attempting to write PostgreSQL 9.0 function

Question

I am attempting to write a function in PostgreSQL 9.0. This will eventually be used in a new aggregate function, but one step at a time. Here is what I have so far:

create or replace function encstate(text,text) returns text as $$
DECLARE
  oldstate alias for $1;
  arg alias for $2;
BEGIN
IF length(oldstate)>0 then
  select 'Encrypted';
else if
  select '';
end if;
END; 
$$ language sql strict immutable;

(I know I'm not yet using the $2 argument.)

The result is:

ERROR:  syntax error at or near "alias"
LINE 3:   oldstate alias for $1;

When I remove the DECLARE block and just refer to the arguments as $1 etc in the body, the result is:

ERROR:  syntax error at or near "if"
LINE 3:   if length($1)>0 then

As far as I can tell, what I have matches examples found on the web, except I could find no examples of functions with an if-statement, so I have no idea what I'm doing wrong. Any help would be appreciated.

Probably you're after `PL/pgSQL` function, not just `SQL` one. — vyegorov, Mar 13 '13 at 11:30
In your example try `case`. No idea if this would help with your real function. — Claude, Mar 13 '13 at 12:08
Is it your intention to return `NULL` on `NULL` input? I suspect you want an empty string '' instead? — Erwin Brandstetter, Mar 13 '13 at 17:03

score 2 · Answer 1 · answered Mar 13 '13 at 12:13

2

I would suggest doing this as an SQL function:

create or replace function encstate(text,text) returns text as $$

SELECT CASE WHEN length($1)>0 then 'Encrypted' ELSE '' END;

$$ language sql strict immutable;

You could also do what you did with the other, but change sql to plpgsql. My suggestion though is that what you can do in an SQL function you should do in one usually. You will get better performance and the planner can do more with it.

answered Mar 13 '13 at 12:13

Chris Travers

25,424
6
65
182

In this case I recommend *not* making the function `strict`, as `strict` functions cannot be inlined by the query planner. Unless `strict` is required for semantics it should be omitted. – Craig Ringer Mar 13 '13 at 12:40
@CraigRinger: [AS discussed under this question](http://stackoverflow.com/questions/8455177/function-executes-faster-without-strict-modifier). But it's worth mentioning, that `STRICT` also impacts the result in this case. – Erwin Brandstetter Mar 13 '13 at 17:14

Clodoaldo Neto · Accepted Answer · 2013-03-13T12:24:40.150

1

If you want a SQL function:

create or replace function encstate(text, text) returns text as $$
select case
    when length($1) > 0 then 'Encrypted'
    else ''
    end
;
$$ language sql strict immutable;

SQL has no variables or control structures as it is not procedural, it is declarative. If you want procedural features then use a plpgsql function:

create or replace function encstate(text, text) returns text as $$
DECLARE
    oldstate alias for $1;
    arg alias for $2;
BEGIN
    IF length(oldstate) > 0 then
        return 'Encrypted';
    else
        return '';
    end if;
END; 
$$ language plpgsql strict immutable;

edited Mar 13 '13 at 12:24

answered Mar 13 '13 at 12:15

Clodoaldo Neto

118,695
26
233
260

More than one person solved it - the clincher in the end was the language declaration at the end. I stuck with sql and used case statement. – NickJ Mar 13 '13 at 17:10
@Nickj: Language declaration can be in various places [as documented in the manual](http://www.postgresql.org/docs/current/interactive/sql-createfunction.html). – Erwin Brandstetter Mar 13 '13 at 17:12

score 1 · Answer 3 · edited May 23 '17 at 10:32

SQL

CREATE OR REPLACE FUNCTION encstate(oldstate text, arg text)
  RETURNS text LANGUAGE SQL IMMUTABLE AS
$func$
SELECT CASE WHEN $1 <> '' THEN 'Encrypted' ELSE '' END
$func$

PL/pgSQL

CREATE OR REPLACE FUNCTION encstate(oldstate text, arg text)
  RETURNS text LANGUAGE plpgsql IMMUTABLE AS
$func$
BEGIN
    IF oldstat <> '' THEN
      RETURN 'Encrypted';
    ELSE
      RETURN '';
    END IF;
END 
$func$;

Major points

The expression length(x) > 0 (x being text) only excludes '' and NULL. Use the 100 % equivalent expression x <> ''. Does the same simpler and faster, regardless of whether the function is declared STRICT or not.
Don't use plpgsql ALIAS if you don't have to. It's only there for compatibility and to rename pre-determined parameter names. The manual actively discourages its use for other purposes. I never use it. Named parameters are available since version 8.1. Simpler, better.
In SQL functions you can refer to parameter names (instead of positional parameters ($1, $2, ..) since PostgreSQL 9.2. It's still a good idea to name parameters even before that, for documentation.
I suspect you do not want to declare this function STRICT (synonym: RETURNS NULL ON NULL INPUT). Like the synonym implies, that returns NULL on (any) NULL input. Seems like you want an empty string ('') instead. There is also a performance implication:
Function executes faster without STRICT modifier?

+1 on not using aliases. Named args can also be determined at run-time by applications and used (the way we do things in LSMB). — Chris Travers, Mar 14 '13 at 00:53

Attempting to write PostgreSQL 9.0 function

3 Answers3

SQL

PL/pgSQL

Major points