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.

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

Answer 1

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.

• Craig Ringer: 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.
• Erwin Brandstetter: @CraigRinger: AS discussed under this question. But it's worth mentioning, that STRICT also impacts the result in this case.

Answer 2

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;

• NickJ: 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.
• Erwin Brandstetter: @Nickj: Language declaration can be in various places as documented in the manual.

Answer 3

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$;

The expression length(x) > 0 (x being type text) only excludes '' and NULL.
The 100 % equivalent expression x <> '' achieves the same simpler and faster, regardless of whether the function is declared STRICT or not.

Only use ALIAS in PL/pgSQL if you have to. It's only there for compatibility and to rename predetermined parameter names. The manual actively discourages 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 ordinal references (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?

• Chris Travers: +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).