[SQL] Extracting user db tabel info from system tables???
QUESTIONS ON USEFULL JOINS ON SYSTEM TABLES FOR USE WITH PHP-WEBINTERFACE
I have some problems on making the right joins on system tables to extract
the
structure of some user defined tables/databases. I use PostgreSQL 7.0.2 on
an
RedHat 7.0 box.
PROBLEM 1: I tried to make a Foreign key constraint from the primary key of
table 'pred' to the table 'prey'. The PRIMARY KEY ("yeartime", "pred",
"pred_age")
of ' pred' should be a Foreign key in 'prey'. Hovever, when I make a dump I
get this:
CREATE TABLE "pred" (
"yeartime" float8 NOT NULL,
"pred" character varying(10) NOT NULL,
"pred_age" int8 NOT NULL,
"stomachn" float8,
"totcon" float8,
"consum" float8,
PRIMARY KEY ("yeartime", "pred", "pred_age")
);
REVOKE ALL on "pred" from PUBLIC;
GRANT SELECT on "pred" to PUBLIC;
GRANT UPDATE,DELETE,SELECT on "pred" to "mac";
CREATE TABLE "prey" (
"yeartime" float8 NOT NULL,
"pred" character varying(10) NOT NULL,
"pred_age" int8 NOT NULL,
"prey" character varying(10) NOT NULL,
"prey_age" int8 NOT NULL,
"wstom" float8,
"stomcon" float8,
PRIMARY KEY ("yeartime", "pred", "pred_age", "prey", "prey_age")
);
REVOKE ALL on "prey" from PUBLIC;
GRANT SELECT on "prey" to PUBLIC;
GRANT UPDATE,DELETE,SELECT on "prey" to "mac";
QUESTION 1): How to define Foreign keys properly
PROBLEM 2:
I try to make some queries on POSTGRES system tables to determine the table
definitions
dynamically in a PHP script- the idea is that I do not want to toutch the
PHP code in case
that the database table structure changes. I can retrieve the structure of
the 'prey' table
primary keys by the following SQL query:
baltic=> SELECT a.attname, ic.relname, i.indisunique, i.indisprimary
FROM pg_class bc, pg_class ic, pg_index i, pg_attribute a
WHERE bc.oid = i.indrelid and ic.oid = i.indexrelid and a.attrelid = bc.oid
and bc.relname = 'prey' and (i.indkey[0] = a.attnum or i.indkey[1] =
a.attnum
or i.indkey[2] = a.attnum or i.indkey[3] = a.attnum or i.indkey[4] =
a.attnum
or i.indkey[5] = a.attnum or i.indkey[6] = a.attnum or i.indkey[7] =
a.attnum)
ORDER BY ic.relname, a.attname;
attname | relname | indisunique | indisprimary
--+---+-+--
pred | prey_pkey | t | t
pred_age | prey_pkey | t | t
prey | prey_pkey | t | t
prey_age | prey_pkey | t | t
yeartime | prey_pkey | t | t
(5 rows)
Question 2: How can I avoid the sequences of OR statements, which are
errorprone (and unelegant)
in case that there are more than 7 fields in the primary key?
PROBLEM 3:
I can get a nice description of all the 'prey' table fields by issuing the
following SQL query:
baltic=> SELECT c.relname, u.usename, c.relacl, a.attname, t.typname,
a.attlen, a.attnotnull
FROM pg_class c, pg_attribute a, pg_type t , pg_user u
WHERE u.usesysid = c.relowner AND c.relname = 'prey' AND a.attnum > 0 AND
a.attrelid = c.oid
AND a.atttypid = t.oid
ORDER BY a.attnum;
relname | usename | relacl | attname | typname | attlen |
attnotnull
-+-+-+--+-++
prey| mac | {"=r","mac=rw"} | yeartime | float8 | 8 | t
prey| mac | {"=r","mac=rw"} | pred | varchar | -1 | t
prey| mac | {"=r","mac=rw"} | pred_age | int8| 8 | t
prey| mac | {"=r","mac=rw"} | prey | varchar | -1 | t
prey| mac | {"=r","mac=rw"} | prey_age | int8| 8 | t
prey| mac | {"=r","mac=rw"} | wstom| float8 | 8 | f
prey| mac | {"=r","mac=rw"} | stomcon | float8 | 8 | f
(7 rows)
QUESTION 3: How do I merge the two above queries to get a table like this
(Outer Join Union???
I know how to emulate outer joints by an Union and Where ... Not In
(select..), but I can't find out
how to join two queries, and not two tables..)
relname | usename | relacl | attname | typname | attlen |
attnotnull | relname | indisunique | indisprimary
-+-+-+--+-++
+---+-+--
prey| mac | {"=r","mac=rw"} | yeartime | float8 | 8 | t
| prey_pkey | t | t
prey| mac | {"=r","mac=rw"} | pred | varchar | -1 | t
| prey_pkey | t | t
prey| mac | {"=r","mac=rw"} | pred_age | int8| 8 | t
| prey_pkey | t | t
prey| mac | {"=r","mac=rw"} | prey | varchar | -1 | t
| prey_pkey | t | t
prey| mac | {"=r","mac=rw"} | prey_age | int8| 8 | t
| prey_pkey | t | t
prey| mac | {"=r","mac=rw"} | wstom| float8 | 8 | f
| NULL` | NULL| NULL
prey| mac | {"=r","mac=rw"} | stomcon | float8 | 8 | f
| NULL` | NULL| NULL
(7 rows)
[SQL] Non-procedural field merging?
I have two tables, foo and foo2: richardh=> select * from foo; a | b ---+- 1 | xxx 1 | yyy richardh=> select * from foo2; c | d ---+--- 1 | And I would like to set d to 'xxxyyy' (i.e. merge entries from b). Of course the following doesn't work because the 'd' seen is the one from before the query starts. richardh=> update foo2 set d = d || foo.b from foo where foo.a=foo2.c; UPDATE 1 richardh=> select * from foo2; c | d ---+- 1 | yyy Now - I can always solve the problem procedurally, merging the values in my application but I was wondering if any of the smarter people on the list have an SQL way of doing it (something with sub-queries?) PS - I realise I might get 'xxxyyy' or 'yyyxxx' without forcing an order but I don't actually care in this case. TIA - Richard Huxton
Re: [SQL] Non-procedural field merging?
"Richard Huxton" <[EMAIL PROTECTED]> writes: > I have two tables, foo and foo2: > richardh=> select * from foo; > a | b > ---+- > 1 | xxx > 1 | yyy > richardh=> select * from foo2; > c | d > ---+--- > 1 | > And I would like to set d to 'xxxyyy' (i.e. merge entries from b). You could do it with a user-defined aggregate function (initial value '' and transition function ||). I am not sure that aggregates work in an intelligent way in UPDATE --- ie, I am not sure it would work to do update foo2 set d = catenate(foo.b) from foo where foo.a=foo2.c; I seem to recall some discussion concluding that that didn't have very well-defined semantics. But you could do SELECT a, catenate(b) INTO TEMP TABLE t1 FROM foo GROUP BY a; and then update into foo2 from the temp table. > PS - I realise I might get 'xxxyyy' or 'yyyxxx' without forcing an order but > I don't actually care in this case. Check. You don't have any control over the order in which input rows will be presented to an aggregate function. regards, tom lane
Re: [SQL] Extracting user db tabel info from system tables???
On Fri, 5 Jan 2001, Marc Cromme wrote:
> I have some problems on making the right joins on system tables to extract
> the
> structure of some user defined tables/databases. I use PostgreSQL 7.0.2 on
> an
> RedHat 7.0 box.
>
> PROBLEM 1: I tried to make a Foreign key constraint from the primary key of
> table 'pred' to the table 'prey'. The PRIMARY KEY ("yeartime", "pred",
> "pred_age")
> of ' pred' should be a Foreign key in 'prey'. Hovever, when I make a dump I
> get this:
Later on in the dump, there should be a line of the form:
CREATE CONSTRANT TRIGGER ...
referencing the tables in question. It'll probably be near the end. We
currently dump the fk constraints as their internal representation
(constraint triggers) rather than as the original constraints.
> PROBLEM 2:
> I try to make some queries on POSTGRES system tables to determine the table
> definitions
> dynamically in a PHP script- the idea is that I do not want to toutch the
> PHP code in case
> that the database table structure changes. I can retrieve the structure of
> the 'prey' table
> primary keys by the following SQL query:
>
> baltic=> SELECT a.attname, ic.relname, i.indisunique, i.indisprimary
> FROM pg_class bc, pg_class ic, pg_index i, pg_attribute a
> WHERE bc.oid = i.indrelid and ic.oid = i.indexrelid and a.attrelid = bc.oid
> and bc.relname = 'prey' and (i.indkey[0] = a.attnum or i.indkey[1] =
> a.attnum
> or i.indkey[2] = a.attnum or i.indkey[3] = a.attnum or i.indkey[4] =
> a.attnum
> or i.indkey[5] = a.attnum or i.indkey[6] = a.attnum or i.indkey[7] =
> a.attnum)
> ORDER BY ic.relname, a.attname;
>
> attname | relname | indisunique | indisprimary
> --+---+-+--
> pred | prey_pkey | t | t
> pred_age | prey_pkey | t | t
> prey | prey_pkey | t | t
> prey_age | prey_pkey | t | t
> yeartime | prey_pkey | t | t
> (5 rows)
>
> Question 2: How can I avoid the sequences of OR statements, which are
> errorprone (and unelegant)
> in case that there are more than 7 fields in the primary key?
You could probably look at the array stuff in contrib for the
element in array functions/operators and use that.
> PROBLEM 3:
> I can get a nice description of all the 'prey' table fields by issuing the
> following SQL query:
>
> baltic=> SELECT c.relname, u.usename, c.relacl, a.attname, t.typname,
> a.attlen, a.attnotnull
> FROM pg_class c, pg_attribute a, pg_type t , pg_user u
> WHERE u.usesysid = c.relowner AND c.relname = 'prey' AND a.attnum > 0 AND
> a.attrelid = c.oid
> AND a.atttypid = t.oid
> ORDER BY a.attnum;
>
> relname | usename | relacl | attname | typname | attlen |
> attnotnull
> -+-+-+--+-++
>
> prey| mac | {"=r","mac=rw"} | yeartime | float8 | 8 | t
> prey| mac | {"=r","mac=rw"} | pred | varchar | -1 | t
> prey| mac | {"=r","mac=rw"} | pred_age | int8| 8 | t
> prey| mac | {"=r","mac=rw"} | prey | varchar | -1 | t
> prey| mac | {"=r","mac=rw"} | prey_age | int8| 8 | t
> prey| mac | {"=r","mac=rw"} | wstom| float8 | 8 | f
> prey| mac | {"=r","mac=rw"} | stomcon | float8 | 8 | f
> (7 rows)
>
>
> QUESTION 3: How do I merge the two above queries to get a table like this
> (Outer Join Union???
> I know how to emulate outer joints by an Union and Where ... Not In
> (select..), but I can't find out
> how to join two queries, and not two tables..)
Your best bet is probably to make views for the two queries and then do
the outer join using those in which case they effectively look like
tables. You could do it without the views, but that'll be kind of
long and hard to read.
> QUESTION 4: How do I extract also information on foreign keys from the
> system tables,
> and add two columns to the above table like the following?
>
> fkey | ftable
> ---+-
> pred_pkey | pred
> pred_pkey | pred
> pred_pkey | pred
> NULL | NULL
> NULL | NULL
> NULL | NULL
> NULL | NULL
The foreign key constraint information is stored in pg_trigger. The
problem is that there's no good way to get the column information from
within sql right now (they're stored as arguments in tgargs).
[SQL] pqReadData()
Hi all, I was trying to simply update single field in one of my tables as given below: fliprdb=# update artist set extrinfo='independent' where artist_id=6; pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. Anyone have idea where the problem is and how it can be fixed? Thank you all in advance. Regards. Najm
Re: [SQL] Casting
Thomas SMETS writes: > I'm removing charaters from a String which should be numbers. > I then want to make calculations on these numbers (calculate the ISBN > number). (You might want to look into contrib/isbn_issn for an isbn type.) > Do I have to cast the char into int's before I can do the calulations. Depends on the calculation. I'd just try to see if it works. When in doubt add casts. > Also I looked in the User manual but could not find the modulo function > where is it ? 5 % 4 mod(5, 4) -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Non-procedural field merging?
From: "Tom Lane" <[EMAIL PROTECTED]> [snipped my Q about merging text fields from one table into another] > You could do it with a user-defined aggregate function (initial > value '' and transition function ||). I am not sure that aggregates > work in an intelligent way in UPDATE --- ie, I am not sure it would > work to do > > update foo2 set d = catenate(foo.b) from foo where foo.a=foo2.c; Actually, (to my surprise) it did work. I used: richardh=> create aggregate catenate(sfunc1=textcat, basetype=text, stype1=text, initcond1=''); CREATE richardh=> select a,catenate(b) from foo group by a; a | catenate ---+-- 1 | xxxyyy (1 row) Then tried the update - worked with no problem, noting that: richardh=> update foo2 set d = catenate(foo.b) from foo where foo.a=foo2.c; UPDATE 1 richardh=> select * from foo2; c | d ---+ 1 | yyyxxx (1 row) The order is reversed between the select and the update! Important lesson in the relationship between SQL and set theory noted (my college lecturers would be proud of me ;-) > I seem to recall some discussion concluding that that didn't have > very well-defined semantics. I can see how you'd have problems if you were expecting the aggregate to return the same value on each run (a vacuum presumably could reorder the values). In my case, this isn't important. I must admit it didn't occur to me you could create your own aggregates without resorting to C. Shame it's not a standard SQL feature. Thanks Tom - don't know how you find the time to give so much help in the lists. - Richard Huxton
Re: [SQL] pqReadData()
Najm Hashmi <[EMAIL PROTECTED]> writes: > fliprdb=# update artist set extrinfo='independent' where artist_id=6; > pqReadData() -- backend closed the channel unexpectedly. Hmm. Looks like you hit a backend crash :-(. Hard to say more with only this amount of info. What PG version are you using? What is the table's declaration? There should be a core file from the backend crash, in $PGDATA/base/fliprdb/core --- can you get a backtrace from it with gdb? regards, tom lane
[SQL] Postgresql database access
Dear Pg experts, I have two databases and i want to refer to one table in a database "X" when i'm using database "Y". Something like this: select * from X.table This causes a parse error. What's the correct sintax? Best regards, Marcos Aurelio Brazil
Re: [SQL] Postgresql database access
"Marcos =?iso-8859-1?Q?Aur=E9lio?= S. da Silva" wrote: >Dear Pg experts, > >I have two databases and i want to refer to one table in a database "X" >when >i'm using database "Y". Something like this: > >select * from X.table > >This causes a parse error. What's the correct sintax? It isn't possible to do this; you can only look at one database at a time. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "And thou shalt love the LORD thy God with all thine heart, and with all thy soul, and with all thy might." Deuteronomy 6:5
