Re: How to return more than one row of data from a
От | MindTerm |
---|---|
Тема | Re: How to return more than one row of data from a |
Дата | |
Msg-id | 20011123162742.21470.qmail@web20203.mail.yahoo.com обсуждение исходный текст |
Ответ на | Re: How to return more than one row of data from a ("Johnny J\xF8rgensen" <pgsql@halfahead.dk>) |
Список | pgsql-sql |
Dear all, Can you create a user defined type by using CREATE TYPE ? M.T. --- "Johnny_J�rgensen" <pgsql@halfahead.dk> wrote: > I have a bit trouble following the problem, but I > think it may be worth mentioning, that you can > return multiple rows by defining the function return > as > > CREATE FUNCTION foo_func(int,int) RETURNS SETOF > <tablename> AS ' > SELECT * FROM <tablename> WHERE intval BETWEEN $1 > AND $2; > ' LANGUAGE 'sql'; > > - hope it helps.. > > *********** REPLY SEPARATOR *********** > > On 23-11-2001 at 04:20 Dino Cherian wrote: > > >Hi, > > > >Thanks Andrew G. Hammond, but it has some problem, > I think and > >suspect. > > > >It seems working, but can it be used in a > multi-user environment. I > >think there will be problem with identification of > which all data > >belongs to whom. > > > >Regards > >Dino > > > >--- "Andrew G. Hammond" <drew@xyzzy.dhs.org> wrote: > >> -----BEGIN PGP SIGNED MESSAGE----- > >> Hash: SHA1 > >> > >> On 2001 November 19 04:22 am, dino ck wrote: > >> > Hi, > >> > > >> > Is there a way to return more than one row of > data from a > >> function in > >> > PL/pgSQL? > >> > > >> > Anybody please help me with an example or a > good resource on the > >> net. > >> > >> I don't know exactly what you're trying to > achieve, BUT, you might > >> want to try > >> a temporary table or even something like this... > >> > >> - -- initialize > >> DROP SEQUENCE multi_marker; DROP TABLE > multi_results, foo; DROP > >> FUNCTION multi_return(text); > >> - -- create > >> CREATE TABLE foo(data TEXT NOT NULL); > >> CREATE SEQUENCE multi_marker; > >> CREATE TABLE multi_results(r_id INTEGER NOT NULL, > data TEXT NOT > >> NULL); > >> CREATE FUNCTION multi_return(text) RETURNS > INTEGER AS ' > >> DECLARE r_idx INTEGER; > >> ins_1 CONSTANT TEXT := ''INSERT INTO > multi_results (r_id, data) > >> SELECT ''; > >> ins_2 CONSTANT TEXT:= '', data || > ''''_add'''' FROM foo WHERE > >> ''; > >> where_clause ALIAS FOR $1; > >> ins_final TEXT; > >> BEGIN r_idx := nextval(''multi_marker''); > >> ins_final := ins_1 || r_idx || ins_2 || > where_clause; > >> RAISE NOTICE ''executing: %'', ins_final; > >> EXECUTE ins_final; > >> RETURN r_idx; > >> END;' LANGUAGE 'plpgsql'; > >> - -- populate > >> INSERT INTO foo VALUES (''); INSERT INTO foo > VALUES ('a'); INSERT > >> INTO foo VALUES ('b'); > >> - -- usage > >> BEGIN; > >> SELECT multi_return('length(data) > 0'::text); > -- returns an > >> index, ie 1 > >> SELECT data FROM multi_results WHERE r_id = 1; > -- get > >> results > >> DELETE FROM multi_results WHERE r_id = 1; > -- cleanup. > >> COMMIT; > >> > >> - -- > >> Andrew G. Hammond mailto:drew@xyzzy.dhs.org > >> http://xyzzy.dhs.org/~drew/ > >> 56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F > > >> 613-389-5481 > >> 5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 > 84B1 > >> "To blow recursion you must first blow recur" -- > me > >> -----BEGIN PGP SIGNATURE----- > >> Version: GnuPG v1.0.6 (GNU/Linux) > >> Comment: For info see http://www.gnupg.org > >> > >> > iEYEARECAAYFAjv9+tUACgkQCT73CrRXhLHGDACeMgpWfE8O1fHOkO7kFuNLNDvd > >> 7XoAn10pv/9enQ9NyetvUp5s32iP3uO8 > >> =57Z4 > >> -----END PGP SIGNATURE----- > >> > >> ---------------------------(end of > >> broadcast)--------------------------- > >> TIP 2: you can get off all lists at once with the > unregister > >> command > >> (send "unregister YourEmailAddressHere" to > >majordomo@postgresql.org) > > > > > >__________________________________________________ > >Do You Yahoo!? > >Yahoo! GeoCities - quick and easy web site hosting, > just $8.95/month. > >http://geocities.yahoo.com/ps/info1 > > > >---------------------------(end of > broadcast)--------------------------- > >TIP 3: if posting/reading through Usenet, please > send an appropriate > >subscribe-nomail command to > majordomo@postgresql.org so that your > >message can get through to the mailing list cleanly > > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster __________________________________________________ Do You Yahoo!? Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month. http://geocities.yahoo.com/ps/info1
В списке pgsql-sql по дате отправления:
Предыдущее
От: "Aasmund Midttun Godal"Дата:
Сообщение: Re: PL/pgSQL examples NOT involving functions