| From: | "Relyea, Mike" <Mike(dot)Relyea(at)xerox(dot)com> |
|---|---|
| To: | <pgsql-general(at)postgresql(dot)org> |
| Subject: | Plpgsql function with unknown number of args |
| Date: | 2005-04-18 18:51:24 |
| Message-ID: | 1806D1F73FCB7F439F2C842EE0627B1801C32853@usa0300ms01.na.xerox.net |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
I need to create my very first function. I'm using 8.0.2 and I need a
function that I can call (from my client app) with an unknown number of
criteria for a select query. The function will then return the results
of the query. In my mind, it would go something like what I've outlined
below. I realize that there are syntax mistakes etc, but this is just
an example:
CREATE TABLE mytable (
a INTEGER UNIQUE PRIMARY KEY,
b VARCHAR(100) NOT NULL,
);
CREATE FUNCTION myfunc(criteria) RETURNS ???? AS $$
BEGIN
BEGIN
FOREACH crit IN criteria
critsql := "b = 'crit' OR "
NEXT crit
END;
PERFORM "SELECT a, b FROM mytable WHERE (" critsql ");"
END;
$$ LANGUAGE plpgsql;
Select * from myfunc(1, 2, 3, 4) would then equate to SELECT a, b FROM
mytable WHERE (b = '1' OR b = '2' OR b = '3' OR b = '4');
Select * from myfunc(9, 21) would equate to SELECT a, b FROM mytable
WHERE (b = '9' OR b = '21');
My question is how do I do that? I've looked through the docs and can't
find what I'm looking for. I'm assuming this is possible because it's a
relatively simple task.
Mike
| From | Date | Subject | |
|---|---|---|---|
| Next Message | ElayaRaja S | 2005-04-18 18:55:39 | Urgent |
| Previous Message | Tom Lane | 2005-04-18 17:26:49 | Re: 'Select INTO" in Execute (dynamic query ) |