postgresql - Advantage of using a SQL function rather than a SELECT -

postgresql - Advantage of using a SQL function rather than a SELECT -

in postgresql 9.0 database, can create url webpage select statement using integer (profile_id) in clause.

in past i've done select whenever convenient, instance using subquery column/field in view. realized create sql function same thing. (this sql function, not plpgsql).

i want know if there advantage, in terms of resources beingness spent, in using function rather select in case this? see below , in advance. not find on topic elsewhere on site. (long-time reader, first-time caller).

the function below.

create or replace function msurl(integer) returns text $body$ select (('https://www.thenameofmywebsite/'::text || case when prof.type = 1 'm'::text else 'f'::text end) || '/handler/'::text) || prof.profile_id profile_url profile prof prof.profile_id = $1; $body$ language sql

to url can either use

select prof.name, select (('https://www.thenameofmywebsite/'::text || case when prof.type = 1 'm'::text else 'f'::text end) || '/handler/'::text) || prof.profile_id profile_url, prof.start_date profile prof, prof.profile_id = id_number;

or tidier version:

select prof.name, msurl(id_number) profile_url, prof.start_date profile prof;

the way using function not have advantage - opposite case: slow downwards select drastically. because each row retrieved main select statement (the 1 calling function) running another select on same table.

functions have advantage when want encapsulate logic of building url. need write function differently more efficient passing row want work with:

create or replace function msurl(profile) returns text $body$ select (('https://www.thenameofmywebsite/' || case when $1.type = 1 'm' else 'f' end) || '/handler/' || $1.profile_id:: profile_url; $body$ language sql;

another alternative have been pass columns need separately, passing row (type) function signature (and calls it) not need changed if logic changes , need more or less columns table.

you can phone call next syntax:

select prof.name, msurl( (prof) ) profile_url, prof.start_date profile prof;

note alias must enclosed in parentheses (prof) when passing function. additional parentheses not optional here.

that way function still gets called each row, doesn't run another select on profile table.

due object oriented way postgres treats such function can phone call it column of table:

select prof.name, prof.msurl profile_url, prof.start_date profile prof;

sql postgresql