Re: [GENERAL] from string to table...?
will trillich wrote: > > um, what's "application layer" mean? :) > sorry - I mean customise queries in the application itself. I tend to build a database abstraction layer which does all this stuff for me. - Richard Huxton ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] from string to table...?
On Fri, Mar 23, 2001 at 07:53:39AM +, Richard Huxton wrote: > will trillich wrote: > > is there a way to write sql/plpgsql that'll take a varchar > > argument (table and field names) and be able to turn it into the > > appropriate query? > > You can use the "EXECUTE" statement in plpgsql (v7.1) to dynamically > build a query. The catch is you can't return rows from a function. You > could use it to build a view and then query that. > > Probably easiest to do in the application layer though. um, what's "application layer" mean? :) -- [EMAIL PROTECTED] http://newbieDoc.sourceforge.net/ -- we need your brain! http://www.dontUthink.com/ -- your brain needs us! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] from string to table...?
On Fri, 23 Mar 2001, will trillich wrote: > let's say you have the name of a table in a string. > > 'mytable' > > you also have field names, such as > > 'lookupfield' 'valuefield' > > which would be assembled like this > > select valuefield from mytable where lookupfield = '?' > > is there a way to write sql/plpgsql that'll take a varchar > argument (table and field names) and be able to turn it into the > appropriate query? > > i can see pg_class contains table info and pg_attribute has info > for the fields ... is there a way (oids maybe?) to direct > postgres to the right table for a search, given the name of the > table in a varchar string? (and if so, how? :) Well, under 7.1, you can use EXECUTE to build query strings inside the procedure and execute them, but you may run into return value problems if you're trying to execute that select and return the value. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] from string to table...?
will trillich wrote: > > let's say you have the name of a table in a string. > > 'mytable' > > you also have field names, such as > > 'lookupfield' 'valuefield' > > which would be assembled like this > > select valuefield from mytable where lookupfield = '?' > > is there a way to write sql/plpgsql that'll take a varchar > argument (table and field names) and be able to turn it into the > appropriate query? You can use the "EXECUTE" statement in plpgsql (v7.1) to dynamically build a query. The catch is you can't return rows from a function. You could use it to build a view and then query that. Probably easiest to do in the application layer though. - Richard Huxton ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] from string to table...?
let's say you have the name of a table in a string. 'mytable' you also have field names, such as 'lookupfield' 'valuefield' which would be assembled like this select valuefield from mytable where lookupfield = '?' is there a way to write sql/plpgsql that'll take a varchar argument (table and field names) and be able to turn it into the appropriate query? i can see pg_class contains table info and pg_attribute has info for the fields ... is there a way (oids maybe?) to direct postgres to the right table for a search, given the name of the table in a varchar string? (and if so, how? :) -- It is always hazardous to ask "Why?" in science, but it is often interesting to do so just the same. -- Isaac Asimov, 'The Genetic Code' [EMAIL PROTECTED] http://newbieDoc.sourceforge.net/ -- we need your brain! http://www.dontUthink.com/ -- your brain needs us! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
