Re: How to return more than one row of data from a

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