Re: [SQL] bug in information_schema?
Am Samstag, 30. Juli 2005 17:15 schrieb Tom Lane: The unconstrained join against pg_user is clearly unnecessary, and in fact I took it out a few days ago. I'm not sure whether the SELECT DISTINCT is still needed --- it might be, if there can be multiple pg_depend entries linking the same entities. That would have been my guess, but it seems that even if a column or table is used multiple times, a dependency is recorded only once, as it should be. It might have been related to the duplicate pg_user mention. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] How to connect ORACLE database from Postgres function using plpgsql/pltclu?
On 8/1/05, Dinesh Pandey [EMAIL PROTECTED] wrote: Is there any way to connect ORACLE database from Postgres function using plpgsql/pltclu? With PLpgSQL I don't think its possible. I don't know how about PLtclU (should be possible), but I'm sure its doable from PLperlU (using DBI). Don't expect it to be easy to set up and fast performing though. ;) Regards, Dawid ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to connect ORACLE database from Postgres function
Title: Re: [SQL] How to connect ORACLE database from Postgres function using plpgsql/pltclu? On 8/1/05 6:35 AM, Dawid Kuroczko [EMAIL PROTECTED] wrote: On 8/1/05, Dinesh Pandey [EMAIL PROTECTED] wrote: Is there any way to connect ORACLE database from Postgres function using plpgsql/pltclu? With PLpgSQL I don't think its possible. I don't know how about PLtclU (should be possible), but I'm sure its doable from PLperlU (using DBI). Don't expect it to be easy to set up and fast performing though. ;) Have a look at DBI-Link. http://pgfoundry.org/projects/dbi-link There is a PostgreSQL tidbits column on DBI-Link here: http://www.pervasive-postgres.com/postgresql/tidbits.asp Sean
Re: [SQL] How to connect ORACLE database from Postgres functionusing plpgsql/pltclu?
Title: Re: [SQL] How to connect ORACLE database from Postgres function using plpgsql/pltclu? Thanks Sean, It great help from you. Thanks Dinesh Pandey From: Sean Davis [mailto:[EMAIL PROTECTED] Sent: Monday, August 01, 2005 4:47 PM To: [EMAIL PROTECTED] Cc: PostgreSQL Subject: Re: [SQL] How to connect ORACLE database from Postgres functionusing plpgsql/pltclu? On 8/1/05 6:35 AM, Dawid Kuroczko [EMAIL PROTECTED] wrote: On 8/1/05, Dinesh Pandey [EMAIL PROTECTED] wrote: Is there any way to connect ORACLE database from Postgres function using plpgsql/pltclu? With PLpgSQL I don't think its possible. I don't know how about PLtclU (should be possible), but I'm sure its doable from PLperlU (using DBI). Don't expect it to be easy to set up and fast performing though. ;) Have a look at DBI-Link. http://pgfoundry.org/projects/dbi-link There is a PostgreSQL tidbits column on DBI-Link here: http://www.pervasive-postgres.com/postgresql/tidbits.asp Sean
[SQL] Calling SQL functions that return sets
Hi all, I have a question on functions returning sets. I have a lot of complex functionality I would like to keep in the DBMS rather than in an application, and I would like this functionality to be available as queries over standard SQL relations. Section 31.4.4 of the docs (http://www.postgresql.org/docs/8.0/static/xfunc-sql.html ) gives an example of calling a function which returns a set, with the function in the SELECT clause. It also states that this is deprecated, and that functions returning sets should be placed in the FROM clause of the query. However, unless I'm missing something it seems that arguments of a FROM-clause function cannot be used in the SELECT or WHERE clause. This seems to me a _huge_ limitation. The example given in 31.4.4 is a function CREATE FUNCTION listchildren(text) RETURNS SETOF text AS ... We can call it (using the deprecated syntax) like this: SELECT name, listchildren(name) FROM nodes; (where nodes is some relation with a text column called name) I like this because I can implement a VIEW: CREATE VIEW name2child AS SELECT name, listchildren(name) FROM nodes; Of course, with the example given, it is easier to do this by directly querying the nodes relation. However, I am interested in the more general case whereby a relation can be implemented with an arbitrarily complex procedural pl/pgsql (or some other language) function, yet appear to have standard relational semantics. If I am now forced to move the function call to the FROM clause, it would appear to be impossible to implement the above view, or any equivalent for a function returning a set. This means that all the wonderful postgres machinery for defining complex functions is absolutely useless to me if I wish to retain standard relational semantics, and not have my code break with some future postgres version. What are the reasons for deprecating the use of the function in the SELECT clause? Am I missing something obvious? Thanks for any insight -- Chris Mungall ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Calling SQL functions that return sets
Chris Mungall [EMAIL PROTECTED] writes: What are the reasons for deprecating the use of the function in the SELECT clause? The semantics of having more than one set-returning function in the target list are, um, poorly thought out. However, we haven't removed the feature because (as you note) there are things you can't do any other way. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Calling SQL functions that return sets
On Mon, 1 Aug 2005, Tom Lane wrote: Chris Mungall [EMAIL PROTECTED] writes: What are the reasons for deprecating the use of the function in the SELECT clause? The semantics of having more than one set-returning function in the target list are, um, poorly thought out. However, we haven't removed the feature because (as you note) there are things you can't do any other way. Would you recommend I press ahead and define views that make use of setof function calling in the select clause? I would need to be reasonably confident that if this is more strongly deprecated in future versions I can at least recode my views (using some as-yet-to-be-defined function definition and calling mechanism) and preserve their existing semantics. Is there any roadmap for how this will be handled in future versions? Understandably, this may not be a high priority. I'm sure you and the other developers have already thought a lot about this, but for what it's worth I'd like to pich in a vote for taking a prolog-esque approach here. In prolog, everything is a relation. A function taking two arguments is really just a 3-ary relation over (arg1,arg2,result). Thus functions that produce more than one value (eg sqrt) don't require any extensions to the relational model. I amn't the slightest bit familair with the Pg internals, and there may be some very good reasons that make this impractical. Coming back to earth, I have a more specific question which follows on from my initial question. If I have a function 'foo' which takes one argument and returns a setof some table or composite type, it seems I am unable to call the function from the select clause. SELECT foo(1,2); ERROR: set-valued function called in context that cannot accept a set SELECT mycol(foo(1,2)); ERROR: set-valued function called in context that cannot accept a set It looks like I may be stuck anyway Cheers Chris regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Calling SQL functions that return sets
Chris Mungall [EMAIL PROTECTED] writes: On Mon, 1 Aug 2005, Tom Lane wrote: Chris Mungall [EMAIL PROTECTED] writes: What are the reasons for deprecating the use of the function in the SELECT clause? The semantics of having more than one set-returning function in the target list are, um, poorly thought out. However, we haven't removed the feature because (as you note) there are things you can't do any other way. Is there any roadmap for how this will be handled in future versions? I think it's reasonably safe to say that we won't remove the feature for at least one or two releases after having a 100% substitute (which the present SRF-in-FROM feature is not, as you know). There has been some speculation that the SQL:2003 LATERAL syntax might offer an adequate substitute, but no one is really working on that yet AFAIK. Coming back to earth, I have a more specific question which follows on from my initial question. If I have a function 'foo' which takes one argument and returns a setof some table or composite type, it seems I am unable to call the function from the select clause. SELECT foo(1,2); ERROR: set-valued function called in context that cannot accept a set The present plpgsql implementation only works for SRF-in-FROM. (Which is something that could probably be fixed, but given the development direction we want to go in, it doesn't seem like a very good use of time...) You can do SRF-in-target-list with SQL-language functions or C-coded functions; I'm not certain offhand about the status of the other PL languages. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings