[SQL] Generic Join on Arrays
Hi, I'm currently on retrieving meta infromation about db-schemas. As I found out, pg_proc relation provides me with data about defined stored procedures. Togehter with other relations as pg_type I can retrieve readable information, like: select proname, pd.description FROM pg_proc pp left outer join pg_description pd on pp.oid=pd.objoid BUT, how about arrays of type "oidvector" (int4-array) containing foreign keys (to be joined) as pg_proc's column "proargtypes" Is there a way to make some kind of indetermine number of JOINs on the single array items? I want to retrieve a list of argument type names, not plain numbers. I want to avoid doing several resolutions oid->name ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Generic Join on Arrays
On Mon, May 30, 2005 at 03:42:49PM +0200, KÖPFERL Robert wrote: > > As I found out, pg_proc relation provides me with data about defined stored > procedures. Togehter with other relations as pg_type I can retrieve readable > information, like: > select proname, pd.description FROM pg_proc pp left outer join > pg_description pd on pp.oid=pd.objoid > > BUT, how about arrays of type "oidvector" (int4-array) containing foreign > keys (to be joined) as pg_proc's column "proargtypes" Have you used ECHO_HIDDEN (or psql -E) to look at the queries that \df generates? Would oidvectortypes() be useful? SELECT proname, oidvectortypes(proargtypes) FROM pg_proc WHERE proname = 'substr'; proname | oidvectortypes -+- substr | bytea, integer substr | text, integer substr | bytea, integer, integer substr | text, integer, integer (4 rows) Another possibility might be to cast the function's oid to regprocedure: SELECT oid::regprocedure FROM pg_proc WHERE proname = 'substr'; oid --- substr(bytea,integer) substr(text,integer) substr(bytea,integer,integer) substr(text,integer,integer) (4 rows) Is either of those what you're looking for? They don't address the problem in the general case, but they might serve in this particular case. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Generic Join on Arrays
Thanks. Since I was just interested in this special case (while the general wasn't interesting, either) this helped me. Also psql -E is a nice new feature to me. |-Original Message- |From: Michael Fuhr [mailto:[EMAIL PROTECTED] |Sent: Montag, 30. Mai 2005 16:09 |To: KÖPFERL Robert |Cc: [email protected] |Subject: Re: [SQL] Generic Join on Arrays | | |On Mon, May 30, 2005 at 03:42:49PM +0200, KÖPFERL Robert wrote: |> |> As I found out, pg_proc relation provides me with data about |defined stored |> procedures. Togehter with other relations as pg_type I can |retrieve readable |> information, like: |> select proname, pd.description FROM pg_proc pp left outer join |> pg_description pd on pp.oid=pd.objoid |> |> BUT, how about arrays of type "oidvector" (int4-array) |containing foreign |> keys (to be joined) as pg_proc's column "proargtypes" | |Have you used ECHO_HIDDEN (or psql -E) to look at the queries that \df |generates? Would oidvectortypes() be useful? | |SELECT proname, oidvectortypes(proargtypes) |FROM pg_proc |WHERE proname = 'substr'; | proname | oidvectortypes |-+- | substr | bytea, integer | substr | text, integer | substr | bytea, integer, integer | substr | text, integer, integer |(4 rows) | |Another possibility might be to cast the function's oid to |regprocedure: | |SELECT oid::regprocedure |FROM pg_proc |WHERE proname = 'substr'; | oid |--- | substr(bytea,integer) | substr(text,integer) | substr(bytea,integer,integer) | substr(text,integer,integer) |(4 rows) | |Is either of those what you're looking for? They don't address the |problem in the general case, but they might serve in this particular |case. | |-- |Michael Fuhr |http://www.fuhr.org/~mfuhr/ | ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Impossible with pl/pgsql?
Hi, I have a function find() that returns a SETOF INT. I further have a function decorate as follows: CREATE TYPE decorate_type AS (id INT, name TEXT, author TEXT, last_change TIMESTAMP); CREATE FUNCTION decorate(INT) RETURNS decorate_type STABLE LANGUAGE 'plpgsql' AS ' -- BLACK BOX '; Now I can do SELECT decorate(4); (4,egg,john,2003-05-05) and I can do SELECT * FROM decorate(4); id | name | author | last_change 4 | egg | john | 2003-05-05 SELECT * FROM decorate(5); id | name | author | last_change 5 | ham | dave | 2004-03-01 Let's say find() gives me 4 and 5: SELECT * FROM find(); find 4 5 Now how would a query look like that involves find() and decorate() and returns id | name | author | last_change 4 | egg | john | 2003-05-05 5 | ham | dave | 2004-03-01 I can't figure this out for the life of me. I also have the impression that that's impossible to do without changing find() or decorate(). Markus -- Markus Bertheau ☭ <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Impossible with pl/pgsql?
On Mon, May 30, 2005 at 05:15:55PM +0200, Markus Bertheau ??? wrote: > > Now how would a query look like that involves find() and decorate() and > returns > > id | name | author | last_change > > 4 | egg | john | 2003-05-05 > 5 | ham | dave | 2004-03-01 Either of the following should work in PostgreSQL 8.0 and later: SELECT (decorate(x)).* FROM find() AS f(x); SELECT (decorate(find)).* FROM find(); A downside is that decorate() will be called once for each output column in each row, as can be seen by adding debugging RAISE statements. So in your example it would be called eight times (2 rows * 4 columns) instead of twice (once for each of 2 rows). -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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: [SQL] Impossible with pl/pgsql?
Markus Bertheau =?UTF-8?Q?=E2=98=AD?= <[EMAIL PROTECTED]> writes: > I have a function find() that returns a SETOF INT. I further have a > function decorate as follows: > CREATE TYPE decorate_type AS (id INT, name TEXT, author TEXT, > last_change TIMESTAMP); > CREATE FUNCTION decorate(INT) > RETURNS decorate_type > Now how would a query look like that involves find() and decorate() and > returns > id | name | author | last_change > > 4 | egg | john | 2003-05-05 > 5 | ham | dave | 2004-03-01 One way is select decorate(find) from find(); which will give you something like decorate -- (4,foo,bar,"2005-05-30 12:14:14.161292") (5,foo,bar,"2005-05-30 12:14:14.161292") (2 rows) If you want the columns of the rowtype broken apart, you can use select (decorate(find)).* from find(); id | name | author |last_change +--++ 4 | foo | bar| 2005-05-30 12:14:17.571481 5 | foo | bar| 2005-05-30 12:14:17.571481 (2 rows) although I believe this will result in multiple evaluations of decorate() per row. If decorate() is expensive you'll want to do something like select (decorate).* from (select decorate(find) from find() offset 0) ss; where the OFFSET clause serves as an optimization fence to prevent the planner from folding this down to the same as the previous version. (This is all assuming PG 8.0 or later) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] (Mis)using the PostgreSQL planner to get estimated row counts
I'm trying to write a PostgreSQL extension to estimate the number of
rows returned by a SELECT statement. Ideally, it'd be invoked along
the lines of "SELECT estimate_row_count('SELECT foo FROM bar INNER
JOIN baz ON (id) WHERE a=b');", and would be useful for estimating
the number of pages in a search result, for example.
I've got as far as figuring out how to get an estimated row count
from a Node object for a query (mostly by looking at the code for
EXPLAIN), but there I'm a bit mired - I can't figure out how to take
a string representing an SQL statement and parse it into a Node
object I can feed to the planner.
So, a couple of questions:
1) Can anyone suggest where I should look in the source for the
requisite functions for parsing an SQL string into a Node I can feed
to the planner so I can get an estimated row count?
2) Is this something that's reasonable to do in the first place? I'm
not sure if extensions are supposed to be allowed to delve into
PostgreSQL's internals this much.
3) Are there any other gotchas around this area? For example, there's
a lot going on with Snapshots and ActiveSnapshot that I really don't
have any idea about.
-Nick Johnson
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
