[SQL] traversing foreign key relationships between tables
Hi,
I have the following problem.
There is a trigger event on a table T. Table t is linked through n
intermediate tables (mostly n = 0) to a primary master Table M.
What I need to do is find the entry in M which corresponds to the
triggered entry in T.
I think in pseudocode it would be like this:
S = set of foreign keys in my entry in T
while S is not empty
{
get s from S
find referenced table t for s
if t = M then return s as primary key into M
put all foreign keys in t into S
}
My problem is:
How to identify the attributes in a table (in pg_attribute) which are
foreign keys into other tables and how to get the relids of these other
tables?
Thank you very much,
Markus
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] problem with date/time constants
Hi, server:PostgreSQl 7.1 why doesn't following statement work? "select now;" THANK YOU! JACK LIU ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] indexing arrays in pgaccess's query interface is failing
[ redirected to pgsql-interfaces, which seems a more appropriate venue ] David Stanaway <[EMAIL PROTECTED]> writes: > --.. And now the query that I am having problems with. > > SELECT itID, itProperties[akID], akName > FROM items, arraykeys; > > In pgaccess, > when I try to execute the same query in query builder, > I get the tcl error dialogue: > Error: invalid command name "akID" Someone isn't quoting the query string correctly on the Tcl side --- [akID] is Tclese for command substitution. Sounds like pgaccess expects the user to quote command punctuation characters that should be passed through. Not sure if that should be regarded as a bug or a feature. It could be considered a feature that you can enter SQL commands with Tcl command substitution performed on them, but it's something that would confuse non-Tcl-users a lot. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Re: pls Help us... (sql question)
Best to ask your questions on the list, so others may find them, with (hopefully) helpful answers in the archives in the future. so, you've got a table with indistinguishable rows. I'm afraid you've got to use an non ANSI extension. Every DB I've ever used has something equivelant. In PostgreSQL, it's the 'oid', so in your case, you'd do: SELECT oid,Name from tablename; and see something like: oid Name --- --- 102453 ibrahim first row 102455 ibrahim second row 103756 ibrahim third row Then, you can delete, comparing on the oid: DELETE FROM tablename WHERE oid=102455; Ross On Tue, Jul 03, 2001 at 03:23:42AM -0700, ibrahim cobanoglu wrote: > Hi. my name is ibrahim > > i have one problem with multiple rows.. > > i have a table named record and this table consist one > field (name) > > in this field there are 3 values such as > Name > --- > ibrahim first row > ibrahim second row > ibrahim third row > > i want to delete only the second row. ( with Ansi > SQL (no cursor, trigger, rowid, rownum,etc.) use > select, count, (whats required!) ) > > but i dont know How to delete.. > > its really improtant for me. > > i will glad > > > > > > __ > Do You Yahoo!? > Get personalized email addresses from Yahoo! Mail > http://personal.mail.yahoo.com/ ---(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
[SQL] ConnecDB() -- couldn't send SSL negotiation packet:
hi, While i am trying to connect pgsql from a perl program, i am getting the error message as ConnectDB() -- couldn't send SSL negotiation packet: errno=9 Bad file descriptor Why i am getting this error? How can i rectify this problem? Remember, i am socksifying my application for some necessary reason. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] simple function crashes my postmaster
Hi,
there is a mystic problem with a simple C function which creates a table.
I minimized the code to reproduce the error. It crashes on two different
Linux systems (SuSE) and with pg 7.0/7.1. I found that the crash occurrs
as soon as I use a SERIAL type in the table creation command.
Please help,
thank you,
Markus
Here is the C function:
bool crash ()
{
if (SPI_connect () < 0)
return (false);
SPI_exec ("CREATE TABLE atl (idx SERIAL)",0);
SPI_finish ();
return (true);
}
Here is the SQL code:
CREATE FUNCTION crash() RETURNS BOOL AS '/usr/lib/mycode.so' LANGUAGE 'C';
SELECT crash();
And this is the backend's log:
{ QUERY
:command 5
:create atl
{ CREATE
:relname atl
:istemp false
:columns (
{ COLUMNDEF
:colname idx
:typename
{ TYPENAME
:name int4
:timezone false
:setof false typmod -1
:arrayBounds <>
}
:is_not_null true
:is_sequence true
:raw_default
{FUNCTION nextval
:args (
{CONST "\"atl_idx_seq""
:typename <>
}
)
:agg_star false
:agg_distinct false
}
:cooked_default <>
:constraints (
{ atl_idx_seq
:type DEFAULT
:raw
{FUNCTION nextval
:args (
{CONST "\"atl_idx_seq""
:typename <>
}
)
:agg_star false
:agg_distinct false
}
:cooked <>
}
{ atl_idx_key
:type UNIQUE (
{ IDENT idx
}
)
}
{ <>
:type NOT NULL
}
)
}
)
:inhRelnames <>
:constraints <>
}
:resultRelation 0
:into <>
:isPortal false
:isBinary false
:isTemp false
:unionall false
:distinctClause <>
:sortClause <>
:rtable <>
:targetlist <>
:qual <>
:groupClause <>
:havingQual <>
:hasAggs false
:hasSubLinks false
:unionClause <>
:intersectClause <>
:limitOffset <>
:limitCount <>
:rowMark <>
}
{ QUERY
:command 5
:index atl_idx_key on atl
{ INDEX
:idxname atl_idx_key
:relname atl
:accessMethod btree
:indexParams (
{ INDEXELEM
:name idx
:args <>
:class <>
:typename <>
}
)
:withClause <>
:whereClause <>
:rangetable <>
:lossy false
:unique true
}
:resultRelation 0
:into <>
:isPortal false
:isBinary false
:isTemp false
:unionall false
:distinctClause <>
:sortClause <>
:rtable <>
:targetlist <>
:qual <>
:groupClause <>
:havingQual <>
:hasAggs false
:hasSubLinks false
:unionClause <>
:intersectClause <>
:limitOffset <>
:limitCount <>
:rowMark <>
}
010626.17:02:16.391 [21378] ERROR: RelationClearRelation: relation 38354
deleted while still in use
010626.17:02:16.391 [21378] AbortCurrentTransaction
010626.17:02:16.392 [21378] NOTICE: mdopen: couldn't open ØÈÞ": No
such file or directory
010626.17:02:16.392 [21378] ERROR: cannot open relation ØÈÞ"
010626.17:02:16.393 [21378] FATAL 2: elog: error during error recovery,
giving up!
010626.17:02:16.393 [21378] proc_exit(2)
010626.17:02:16.393 [21378] shmem_exit(2)
010626.17:02:16.393 [21378] exit(2)
/usr/bin/postmaster: reaping dead processes...
/usr/bin/postmaster: CleanupProc: pid 21378 exited with status 512
Server process (pid 21378) exited with status 512 at Tue Jun 26 17:02:16
2001
Terminating any active server processes...
Server processes were terminated at Tue Jun 26 17:02:16 2001
Reinitializing shared memory and semaphores
010626.17:02:16.397 [21346] shmem_exit(0)
binding ShmemCreate(key=52e325, size=1104896)
010626.17:02:16.398 [21379] DEBUG: Data Base System is starting up at Tue
Jun 26 17:02:16 2001
010626.17:02:16.398 [21379] DEBUG: Data Base System was interrupted being
in production at Tue Jun 26 17:02:12 2001
010626.17:02:16.399 [21379] DEBUG: Data Base System is in production
state at Tue Jun 26 17:02:16 2001
010626.17:02:16.399 [21379] proc_exit(0)
010626.17:02:16.399 [21379] shmem_exit(0)
010626.17:02:16.399 [21379] exit(0)
/usr/bin/postmaster: reaping dead processes...
010626.17:02:19.997 [21346] pmdie 2
Fast Shutdown request at Tue Jun 26 17:02:19 2001
010626.17:02:19.998 [21380] DEBUG: Data Base System shutting down at Tue
Jun 26 17:02:19 2001
010626.17:02:19.999 [21380] DEBUG: Data Base System shut down at Tue Jun
26 17:02:19 2001
010626.17:02:19.999 [2138
[SQL] drop table if exists
How can I duplicate the behavior of: DROP TABLE IF EXISTS mytable; CREATE TABLE mytable ( blah, blah ); INSERT INTO mytable (blah) VALUES (blah); in other words, so that I have a single sql file that restores the database to a known state. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Re: drop table if exists
Doesn't work. I like wrapping up the entire file in a transaction so that if I make a stupid syntax error or the like, I can just do a rollback. Because of that, the transaction enters abort state. I suppose I can just stop using transactions and use this method. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Re: drop table if exists
Just drop the table using "DROP TABLE mytable;" and ignore the error... I'm sure there are fancy ways of doing it by accessing system tables, but the above works for me. On Tue, 3 Jul 2001, Jason Watkins wrote: > How can I duplicate the behavior of: > > DROP TABLE IF EXISTS mytable; > > CREATE TABLE mytable ( > blah, > blah > ); > > INSERT INTO mytable > (blah) > VALUES > (blah); > > in other words, so that I have a single sql file that restores the database > to a known state. > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] simple function crashes my postmaster
Meggus <[EMAIL PROTECTED]> writes: > there is a mystic problem with a simple C function which creates a table. > I minimized the code to reproduce the error. It crashes on two different > Linux systems (SuSE) and with pg 7.0/7.1. I found that the crash occurrs > as soon as I use a SERIAL type in the table creation command. I can't duplicate the problem in 7.1.2 (nor current sources). Please update. If you still see the problem with 7.1.2, try setting a breakpoint at elog() to obtain a stack trace from the point of the first error message. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] count(*)
>> You probably mean: >> select car, tit, (select count(*) from auto) from auto I think he probably wants select car, tit, count(*) from auto group by car, tit regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] problem with date/time constants
Hi, 'now' is a function... so try 'select now();' On Tue, 3 Jul 2001, datactrl wrote: > Hi, > server:PostgreSQl 7.1 > why doesn't following statement work? > "select now;" > > THANK YOU! > JACK LIU > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] indexing arrays in pgaccess's query interface is failing
Hi there, I am having some difficulties with using arrays in pgaccess
relating to arrays.
Here is an example schema:
CREATE TABLE arraykeys (
akID int,
akName varchar(12),
PRIMARY KEY(akID)
);
CREATE TABLE items (
itID serial,
itProperties bool[],
PRIMARY KEY(itID)
);
--.. And some Data
INSERT INTO arraykeys VALUES (1,'Active');
INSERT INTO arraykeys VALUES (2,'Overdue');
INSERT INTO arraykeys VALUES (3,'Local');
INSERT INTO items (itProperties) VALUES ( '{1,0,1}');
INSERT INTO items (itProperties) VALUES ( '{0,1,1}');
--.. And now the query that I am having problems with.
SELECT itID, itProperties[akID], akName
FROM items, arraykeys;
In the readline client psql, the above select statement works perfectly
scratch-# FROM items, arraykeys;
itid | itproperties | akname
--+--+-
1 | t| Active
1 | f| Overdue
1 | t| Local
2 | f| Active
2 | t| Overdue
2 | t| Local
(6 rows)
However
In pgaccess,
when I try to execute the same query in query builder,
I get the tcl error dialogue:
Error: invalid command name "akID"
Is there an alternate way indexing arrays in queries that I should be
using?
Or is pgaccess just not suitable for this class of queries!
--
Best Regards
David Stanaway
.-
Technology Manager - Australia's Premier Internet Broadcasters
[EMAIL PROTECTED] Office +612 9357 1699
'-
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
