CREATE TYPE public."korisnik" AS (
"ime" VARCHAR(20),
"prezime" VARCHAR(20),
"adresa" VARCHAR(50) ) ;
CREATE OR REPLACE FUNCTION public."sf_korisnik" (pojam varchar) RETURNS SETOF "korisnik" AS
$body$
SELECT
ime,
prezime,
adresa
FROM "Partner"
WHERE prezime LIKE ('%' || TRIM($1) || '%')
$body$
LANGUAGE 'sql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
a ako ti treba samo jedan red dodaj LIMIT 1, znaci :
CREATE OR REPLACE FUNCTION public."sf_korisnik" (pojam varchar) RETURNS SETOF "korisnik" AS
$body$
SELECT
ime,
prezime,
adresa
FROM "Partner"
WHERE prezime LIKE ('%' || TRIM($1) || '%') LIMIT 1
$body$
LANGUAGE 'sql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
CREATE TYPE korisnik_type AS (ime varchar(20),prezime varchar(20), adresa varchar(50));
CREATE OR REPLACE FUNCTION sf_korisnik (pojam varchar(10)) RETURNS SETOF "korisnik_type" AS $sf_korisnik$
DECLARE
xpojam alias for $1;
r RECORD;
BEGIN
for r in SELECT ime, prezime, adresa FROM "Partner"
WHERE prezime LIKE ('%' || TRIM(xpojam) || '% LOOP
return next r;
END LOOP;
END;
$sf_korisnik$ LANGUAGE 'plpgsql';
To je bilo najavljeno za verziju 8.3 ali nije implementirano. Nadam se da će se pojaviti u verziji 8.4 koja bi trebala da osvane uskoro.
"The best code is no code at all." - Zidar (ES član) "Biggest obstacle to learning
SQL is unlearning procedural
programming." - Joe
Celko "Minimize code, maximize data." - A. Neil Pappalardo