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!
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.
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;
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 );
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:
text as argument type — there's no point in limiting argument length in Postgres, in which there's no difference in representation;immutable, so a database can cache it's result from the same argument, which can be faster;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.
EXISTS()in the query, or even a lookup table. Functions are costly, see theimmutable strictoptions+remark in @Tometzky 's answer below.