Postgresql function with if statement
10,246
Solution 1
create or replace function getf(arg character varying(255)) returns int as $$
begin
if arg = 'a' then
return 1;
else
return 2;
end if;
end; $$ language plpgsql;
Note that this is a PL/pgSQL function.
The online manual has an excellent chapter on PL/pgSQL. That should provide everything you need to get started writing procedural function with ample support for logical branching.
Solution 2
Using sql
language, you can do it using case when:
create or replace function getf(arg character varying(255)) returns int as
$$
select case
when arg = 'a'
then 1
else 2
end
$$ language sql;
Solution 3
A pure SQL function. It is ugly, because SQL does not have IF
. (you could use CASE
instead, which is ugly, too) The DISTINCT FROM
is needed to catch NULLs.
DROP function getf(_arg character varying(255) );
create or replace function getf(_arg character varying(255)) returns INTEGER
as $$
SELECT 1::integer
WHERE _arg = 'a'
UNION ALL
SELECT 2::integer
WHERE _arg IS DISTINCT FROM 'a'
;
$$ language sql;
SELECT getf( 'a' );
SELECT getf( 'b' );
SELECT getf( NULL );
Solution 4
create function getf(arg text) returns int
immutable strict language sql as $$
select case
when arg = 'a'
then 1
else 2
end
$$;
This is almost like Houari's answer, but:
- it uses
text
as argument type — there's no point in limiting argument length in Postgres, in which there's no difference in representation; - it is
immutable
, so a database can cache it's result from the same argument, which can be faster; - it is
strict
, so it would not be run on NULL values and simply return NULL, as it's probably a programming error for it to be used on a NULL value anyway, and it could mask this error and make it harder to debug if it would return 2 for NULL.
Author by
Edijs Petersons
Updated on June 14, 2022Comments
-
Edijs Petersons almost 2 years
How can I make this pseudo code to work in Postgresql:
create or replace function getf(arg character varying(255)) returns int as $$ if arg = 'a' then return 1; else return 2; $$ language sql;
Based on argument I need to return some values and there is no other table I need to query. Just need to build a logic inside function. Tried to replace if with when clause, but could not figure out how to do it.
Thanks!