[SQL] Count Columns
I haven't done very many complex queries in sql, and maybe Im thinking about my problem wrong but: Is there a way to count the number of null or not null columns in a row and have an output column that has that count as the value? I want to create a ranking of the row based upon the number of not null columns are in the row. I want to have to use as few seperate queries as possible. The table that i want to do the query on is either a view or a temporary table, I guess depending on if I can do this easily or if brut force is required, Or if I have to think of a new way to solve my problem. The table is like CREATE TABLE myTbl ( name varchar(5) primary key, a varchar(5), b varchar(5), c varchar(5) ); ending table looks like name | a | b | c | foo | A | | C | bar | A | B | | baz | A | B | C | and I want the result to look like foo 2 bar 2 baz 3 Thanks in Advance ---(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] LOOP?
I am writing a trigger to inspect a row of a temporary table to determine if
the row has any null columns, I will return as soon as I find a null
column. I ran across this in the manual and It seems like I could use
The FOR-IN-EXECUTE statement is another way to iterate over rows:
[<>]
FOR record_or_row IN EXECUTE text_expression LOOP
statements
END LOOP;
postgresql has a RECORD type which is an abstract row.
Does it have any fields to tell the length?
Does it have an accessor function like new.element(index};
I am trying to write a general function where from instance to instance the
columns in the record change. If I can't do this I will have to create a
new function for each temporary table.
Is this possible or should I quit looking;
FOR record IN {something related to NEW } LOOP
END LOOP;
---(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] LinkedList
I have a table that I created that implements a linked list. I am not an expert SQL developer and was wondering if there are known ways to traverse the linked lists. Any information that can point me in the direction to figure this out would be appreciated. The table contains many linked lists based upon the head of the list and I need to extract all of the nodes that make up a list. The lists are simple with a item and a link to the history item so it goes kind of like: 1, 0 3, 1 7, 3 9, 7 ... Any suggestions would be helpful, or I will have to implement the table differently. Thanks Ray Madigan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] LinkedList
Scott, Thanks for your reply, I tried what you said, worked around a few things but I am still stuck. The main reason is I didn't do an adequate job of explaining the situation. The table implements many linked lists and I want to traverse one of them given the end of the list. Say the table contains h | v | j 1 0 100 3 1 300 5 3 500 7 5 700 2 0 200 4 2 400 6 4 600 8 6 800 If I specify t.h = 8 I want to traverse the even part of the table If I specify t.h = 7 I want to traverse the odd part of the table If you can send me to a book to read I am willing Thanks -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Scott Marlowe Sent: Wednesday, April 26, 2006 8:59 AM To: Ray Madigan Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] LinkedList On Wed, 2006-04-26 at 11:09, Ray Madigan wrote: > I have a table that I created that implements a linked list. I am not an > expert SQL developer and was wondering if there are known ways to traverse > the linked lists. Any information that can point me in the direction to > figure this out would be appreciated. The table contains many linked lists > based upon the head of the list and I need to extract all of the nodes that > make up a list. The lists are simple with a item and a link to the history > item so it goes kind of like: > > 1, 0 > 3, 1 > 7, 3 > 9, 7 > ... > > Any suggestions would be helpful, or I will have to implement the table > differently. You should be able to do this with a fairly simple self-join... select a.id, b.aid, a.field1, b.field1 from mytable a join mytable b on (a.id=b.aid) Or something like that. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL]Linked List
I have a table that I created that implements a linked list. I am not an expert SQL developer and was wondering if there are known ways to traverse the linked lists. The table contains many linked lists based upon the head of the list and I need to extract all of the nodes that make up a list. The lists are simple with a item and a link to the history item so it goes kind of like: 1, 0 3, 1 7, 3 9, 7 ... Any suggestions would be helpful, or I will have to implement the table differently. Thanks Ray Madigan ---(end of broadcast)--- TIP 1: 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] Trigger on select :-(
What I want is to have a trigger on select, but since I have searched the archives I know that is not possible. I also read that view rules is the technology is whats available. Having never used views before I can't seem to get my mind to figure out what I need to do to solve my problem. I have a table that defines a key that I will use in another table in a list of items defined in the previous table. The tables are CREATE TABLE Foo ( INTEGER key not null primary key, ... characteristics for the Foo item ); CREATE TABLE Catalog ( INTEGER FooKey FOREIGN KEY, ... other catalog data ); The catalog will have rows that reference the elements in the Foo table. I also have another table that references the Foo table CREATE TABLE ToDo ( INTEGER FooKey FOREIGN KEY, ... other ToDo information. What I want to do is when I do a SELECT on the Catalog and deliver the result to the user, I want to check to see if the FooKey is in the users ToDo table and set the value of a column isToDo to true or false depending on if the FooKey exists in the ToDo table for the user. If I were building a table I would use a trigger and select on the row in the ToDo table. Please, all I want is an idea where to start, what I should read and I will figure out how to do it. Thanks in advance. <> -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Rules with sequence columns
I have the following situation that I would appreciate your input on: I have a table with a column that I use to uniquely identify its rows. The table also has a rule on insert that stores the row identifier into another table for reference at some other point. The table is defined as CREATE SEQUENCE foo_seq; CREATE TABLE foo ( fooK INTEGER DEFAULT NEXTVAL ( 'foo_seq' ), fooN VARCHAR(32) NOT NULL UNIQUE, link INTEGER NOT NULL DEFAULT 0 ); The rule does an insert into another table and I have implemented the rule in two ways. CREATE RULE insertCD AS ON INSERT TO foo DO INSERT INTO cdFoo ( contextK, componentK ) SELECT currval ( 'foo_seq' ), componentK FROM Component WHERE componentN = 'Division'; or CREATE RULE insertCD AS ON INSERT TO foo DO INSERT INTO cdFoo ( contextK, componentK ) SELECT new.fooK, componentK FROM Component WHERE componentN = 'Division'; The situation is that every time the rule fires, the foo sequence is incremented for each row in the foo table. and the reference value is not the same in the table. I have tried to take the default nextval ( 'foo_seq' ) from the row initialization and move it to the insert insert into foo ( fook, fooN ) values ( nextval ( 'foo_seq' ), 'Name' ); with the same result. The only way I have been able to make it work is ugly. int fooK = select nextval ( 'foo_seq' ); insert into foo ( fooK, fooN ) values ( fooK, 'Name' ); Does anyone have any suggestion? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Computed table name in function
I thought that the documentation said I couldn't use EXECUTE on a SELECT INTO? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Scott Marlowe Sent: Wednesday, October 10, 2007 11:10 AM To: Ray Madigan Cc: Pgsql-Sql Subject: Re: [SQL] Computed table name in function On 10/10/07, Ray Madigan <[EMAIL PROTECTED]> wrote: > I have a problem that I don't know where to look to understand the problem. > > I have a function that I first select to get a table name followed by > another select into on that table name. If someone could tell me where to > look to solve this problem I would appreciate it. > > It is something like > > DECLARE rec1 Record; > rec2 Record; > > SELECT aName INTO rec1 FROM tableA WHERE new.xxx = ?; > > IF FOUND THEN > > SELECT * INTO rec2 FROM rec1.aName where new.xxx = ?; You have to build your query as a string then use execute on it. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Computed table name in function
I have a problem that I don't know where to look to understand the problem. I have a function that I first select to get a table name followed by another select into on that table name. If someone could tell me where to look to solve this problem I would appreciate it. It is something like DECLARE rec1 Record; rec2 Record; SELECT aName INTO rec1 FROM tableA WHERE new.xxx = ?; IF FOUND THEN SELECT * INTO rec2 FROM rec1.aName where new.xxx = ?; Thanks ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] Select in From clause
I have never seen this done before, but it seems like it is supposed to work from reading the manual. I want to be able to get a table name from another table and use it in the from clause of a select. Something like SELECT * FROM (SELECT name FROM bar WHERE conditions) AS b WHERE b.condition = xxx; which translates to something like SELECT * FROM Dealer AS b WHERE b.zipcode = 12345; The translated version works but the SELECT in FROM version reports that b.condition does not exist. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Except without elimination of non-distinct rows
Is there any way to use except that won't remove the non-distinct rows from the left side of the query? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Except without elimination of non-distinct rows
I tried using EXCEPT ALL but it seems to only remove one row when there is more then one matching row? Thanks Ray Madigan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Tom Lane Sent: Monday, February 04, 2008 6:20 PM To: Ray Madigan Cc: Pgsql-Sql Subject: Re: [SQL] Except without elimination of non-distinct rows "Ray Madigan" <[EMAIL PROTECTED]> writes: > Is there any way to use except that won't remove the non-distinct rows from > the left side of the query? EXCEPT ALL might or might not do what you're hoping for. Check the manual (I think the SELECT reference page is what to look at). regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
