Re: [SQL] add column if doesn't exist
c == [EMAIL PROTECTED] writes: c> I set up a stored procedure to do this for Slony-I... Replace c> @NAMESPACE@ with your favorite namespace, and slon_quote_brute can c> likely be treated as an identity function unless you use silly c> namespace names :-). Thanks. -- Brandon ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Why doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT
"Ferindo Middleton Jr" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Is there some reason why the SERIAL data type doesn't automatically have a
> UNIQUE CONSTRAINT. It seems that the main reason for using it is so that
> the value for this field keeps changing automatically and is never null so
> any one record can be identified using it- So why not imply that it is
> always be UNIQUE anyway. I mean, if you were to force another value on a
> SERIAL field that already had that same value, the would through the
> sequence tracking the the fields current value off any way, so it just
> makes sense to me to not let a serial field be duplicated. Let's take a
> poll. Is there anyone out there who actually uses the SERIAL data type who
> would not want it to be UNIQUE?
>
> Ferindo
>
> ---(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
>
You are correct... serials don't have unique constraints unless they are
also defined as a primary key...
It seems to me that all you need to do is make your serial value a primary
key in your DDL... (which is the same as defining a unique constraint...)
consider the following:
CREATE TABLE sys_test (
id serial NOT NULL PRIMARY KEY,
txt text not null
) WITH OIDS;
INSERT INTO sys_test(txt) VALUES ('A');
INSERT INTO sys_test(txt) VALUES ('B');
-- INSERT statement #3 throws an expected error
INSERT INTO sys_test(id, txt) VALUES (1, 'C'); // THROWS UNIQUE CONTRAINT
ERROR AS EXPECTED !!!
SELECT * FROM sys_test;
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[SQL] Sending function parametars within EXECUTE ''SELECT...
I've learned that one can't use temporary tables within the function unless EXECUTE'd the SELECTS from that temp table. So, I have a function like this: CREATE FUNCTION Foo1(int4, int4) RETURNS SETOF myType AS ' DECLARE aDataId ALIAS FOR $1; aBid ALIAS FOR $2; return myType; rec record; BEGIN CREATE TEMP TABLE tmpTbl AS SELECT col1, col2 FROM t1 JOIN t2 ON t1.c1 = t2.c3 WHERE t1.c4 = aDataId; FOR rec IN EXECUTE ''SELECT COUNT(col1) AS num FROM tmpTbl WHERE col2 = aBid'' LOOP return.myType = rec.num; END LOOP; RETURN NEXT return; RETURN; END ' language 'pgplsql' Now, when I try to call that function, i get an error that aBid is unknown column name. How can I pass the aBid value to the SELECT statement inside the EXECUTE? I'm using temp table because the tables from where to gather the data are huge. If I'd be using views instead, it'd take too much time. I tought that storing only a small fraction of the data (1/1000 of the data is put into the temp table), and then performing calculations on that temp table would be much faster. I just don't know how to pass parameters to the EXECUTE SELECT. Any help here would be appreciated. Mike P.S. I tried above code on pg8.0.3 and 8.1beta2, with same results. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Sending function parametars within EXECUTE ''SELECT...
"Mario Splivalo" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I've learned that one can't use temporary tables within the function > unless > EXECUTE'd the SELECTS from that temp table. > > So, I have a function like this: > > CREATE FUNCTION Foo1(int4, int4) RETURNS SETOF myType > AS > ' > DECLARE >aDataId ALIAS FOR $1; >aBid ALIAS FOR $2; >return myType; >rec record; > BEGIN >CREATE TEMP TABLE tmpTbl >AS >SELECT col1, col2 FROM t1 JOIN t2 ON t1.c1 = t2.c3 WHERE t1.c4 = > aDataId; > >FOR rec IN EXECUTE ''SELECT COUNT(col1) AS num FROM tmpTbl WHERE > col2 = aBid'' >LOOP >return.myType = rec.num; >END LOOP; > >RETURN NEXT return; >RETURN; > END > ' language 'pgplsql' > > > Now, when I try to call that function, i get an error that aBid is > unknown > column name. How can I pass the aBid value to the SELECT statement > inside > the EXECUTE? > > I'm using temp table because the tables from where to gather the data > are > huge. If I'd be using views instead, it'd take too much time. I tought > that > storing only a small fraction of the data (1/1000 of the data is put > into > the temp table), and then performing calculations on that temp table > would > be much faster. I just don't know how to pass parameters to the EXECUTE > SELECT. > > Any help here would be appreciated. > >Mike > > P.S. I tried above code on pg8.0.3 and 8.1beta2, with same results. > > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings > FOR rec IN EXECUTE ''SELECT COUNT(col1) AS num FROM tmpTbl WHERE col2 = " || aBid || '' LOOP ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Problem with function and trigger...
I have a function declared as such: CREATE OR REPLACE FUNCTION thread_sync() RETURNS trigger AS $$ BEGIN IF TG_OP = 'DELETE' AND OLD.deleted = FALSE THEN UPDATE member SET total_threads=total_threads-1 WHERE id=OLD.member_id; RETURN OLD; ELSEIF TG_OP = 'INSERT' THEN UPDATE member SET total_threads=total_threads+1 WHERE id=NEW.member_id; RETURN NEW; ELSEIF TG_OP = 'UPDATE' AND NEW.deleted = TRUE THEN UPDATE member SET total_threads=total_threads-1 WHERE id=NEW.member_id; RETURN NEW; ELSEIF TG_OP = 'UPDATE' AND NEW.deleted = FALSE THEN UPDATE member SET total_threads=total_threads+1 WHERE id=NEW.member_id; RETURN NEW; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; And the trigger for it: CREATE TRIGGER thread_sync AFTER INSERT OR DELETE OR UPDATE ON thread FOR EACH ROW EXECUTE PROCEDURE thread_sync(); creating the function works fine, as well as creating the trigger, but when I go to insert a row, I get the following message: bcodev=> insert into thread (member_id,subject,category_id,last_member_id) values (1,'hi there this is a test',1,1); ERROR: record "old" is not assigned yet DETAIL: The tuple structure of a not-yet-assigned record is indeterminate. CONTEXT: PL/pgSQL function "thread_sync" line 2 at if What am I failing to understand with this? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Problem with function and trigger...
Ian Meyer <[EMAIL PROTECTED]> writes: > IF TG_OP = 'DELETE' AND OLD.deleted = FALSE THEN > ERROR: record "old" is not assigned yet > DETAIL: The tuple structure of a not-yet-assigned record is indeterminate. > CONTEXT: PL/pgSQL function "thread_sync" line 2 at if > What am I failing to understand with this? We don't guarantee short-circuit evaluation of boolean expressions. You'll have to break that into two IFs, ie, IF TG_OP = 'DELETE' THEN IF ... test on OLD.something ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Selecting count of details along with details columns
Dear pgsql-admin members:
Having 2 tables:
CREATE TABLE T2 (
idserial PRIMARY KEY,
T2_name text
);
CREATE TABLE T1 (
idserial PRIMARY KEY,
T1_name text,
fk_t2 int4REFERENCES t2
);
And some rows:
INSERT INTO T2 (T2_name) VALUES('T2-N1');
INSERT INTO T2 (T2_name) VALUES('T2-N2');
INSERT INTO T2 (T2_name) VALUES('T2-N3');
INSERT INTO T2 (T2_name) VALUES('T2-N4');
INSERT INTO T1 (T1_name, fk_t2) VALUES('T1-AAA', 1);
INSERT INTO T1 (T1_name, fk_t2) VALUES('T1-BBB', 2);
INSERT INTO T1 (T1_name, fk_t2) VALUES('T1-CCC', 2);
INSERT INTO T1 (T1_name, fk_t2) VALUES('T1-DDD', 2);
INSERT INTO T1 (T1_name, fk_t2) VALUES('T1-EEE', 3);
INSERT INTO T1 (T1_name, fk_t2) VALUES('T1-FFF', 3);
It is possible to show how many details exist and to limit result with HAVING:
SELECT T2.T2_name, COUNT(T1.id) AS xx
FROM T2, T1
WHERE T2.id = T1.fk_t2
GROUP BY T2.T2_name
HAVING COUNT(T1.id) > 1
ORDER BY xx DESC;
t2_name | xx
-+
T2-N2 | 3
T2-N3 | 2
(2 rows)
Adding column t1_name to the result set breaks COUNT(T1.id):
SELECT T2.T2_name, T1.T1_name, COUNT(T1.id) AS xx
FROM T2, T1
WHERE T2.id = T1.fk_t2
GROUP BY T2.T2_name, T1.T1_name
HAVING COUNT(T1.id) > 1
ORDER BY xx DESC;
t2_name | t1_name | xx
-+-+
(0 rows)
How can I do this with pg ?
Looking for a workaround, I learned that aggregate functions are not allowed in WHERE clauses.
Question: Is this conform with the standard?
Sorry, if this has been discussed earlier.
Axel
Axel Rau, Frankfurt, Germany +49-69-951418-0
Re: [SQL] changing a column's position in table, how do you do that
Ferindo Middleton Jr wrote: Is there a way to change the position attribute of a column in a table? I have data that I need to import into various tables in my db on a consistent basis... I usually us e the COPY ... FROM query but I can't control the -order- of the fields my client dumps the data so I would like to be able to change the position the columns in my table to be able to better align the data to be imported with the format of my table. I was thinking I could do something like "ALTER TABLE ALTER COLUMN ... " or something like that to change the columns position in the table but I can't figure out how. Ferindo ---(end of broadcast)--- TIP 6: explain analyze is your friend Constantly changing the column order of a table doesn't make sense (and is a pain!). I would suggest setting up a temporary staging table with generic column names to take in the input data and then using SQL to move it into its permanent location. -- Daryl "We want great men who, when fortune frowns, will not be discouraged." -- Colonel Henry Knox, 1776 ---(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
Re: [SQL] Problem with function and trigger...
On 9/28/05, Tom Lane <[EMAIL PROTECTED]> wrote: > Ian Meyer <[EMAIL PROTECTED]> writes: > > IF TG_OP = 'DELETE' AND OLD.deleted = FALSE THEN > > > ERROR: record "old" is not assigned yet > > DETAIL: The tuple structure of a not-yet-assigned record is indeterminate. > > CONTEXT: PL/pgSQL function "thread_sync" line 2 at if > > > What am I failing to understand with this? > > We don't guarantee short-circuit evaluation of boolean expressions. > You'll have to break that into two IFs, ie, > > IF TG_OP = 'DELETE' THEN > IF ... test on OLD.something ... > > regards, tom lane > Ah ha.. that makes sense. I fixed it to be: CREATE OR REPLACE FUNCTION thread_sync() RETURNS trigger AS $$ BEGIN IF TG_OP = 'DELETE' THEN UPDATE member SET total_threads=total_threads-1 WHERE id=OLD.member_id; RETURN OLD; ELSEIF TG_OP = 'INSERT' THEN UPDATE member SET total_threads=total_threads+1 WHERE id=NEW.member_id; RETURN NEW; ELSEIF TG_OP = 'UPDATE' THEN IF NEW.deleted != OLD.deleted THEN IF NEW.deleted = TRUE THEN UPDATE member SET total_threads=total_threads-1 WHERE id=NEW.member_id; RETURN NEW; ELSEIF NEW.deleted = FALSE then UPDATE member SET total_threads=total_threads+1 WHERE id=NEW.member_id; RETURN NEW; END IF; RETURN NULL; END IF; RETURN NULL; END IF; END; $$ LANGUAGE plpgsql; Here's my next question(s)... If i have 2 rows in the thread table and I delete 1 of the rows with: delete from thread where id=26; it ends up decrementing the total_threads value by 2, instead of one. bcodev=> select id, total_threads from member; id | total_threads +--- 1 | 2 (1 row) bcodev=> delete from thread where id=37; DELETE 1 bcodev=> select id, total_threads from member; id | total_threads +--- 1 | 0 (1 row) I'm confused, to say the least. Thanks for all the help. Ian ---(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
Re: [SQL] changing a column's position in table, how do you do that
On Sep 29, 2005, at 4:38 , Daryl Richter wrote: Ferindo Middleton Jr wrote: Is there a way to change the position attribute of a column in a table? I have data that I need to import into various tables in my db on a consistent basis... I usually us e the COPY ... FROM query but I can't control the -order- of the fields my client dumps the data so I would like to be able to change the position the columns in my table to be able to better align the data to be imported with the format of my table. I was thinking I could do something like "ALTER TABLE ALTER COLUMN ... " or something like that to change the columns position in the table but I can't figure out how. Ferindo ---(end of broadcast)--- TIP 6: explain analyze is your friend Constantly changing the column order of a table doesn't make sense (and is a pain!). I would suggest setting up a temporary staging table with generic column names to take in the input data and then using SQL to move it into its permanent location. Or specify the columns directly in the copy statement. Instead of copy foo from , do copy foo (bar,baz,bat) from . Then you only have to change the copy statement. While SQL does allow columns to be referenced by column order, it's far, far better to consider the columns of a table to be unordered and (nearly) always specify your columns explicitly. It gives you much more flexibility. Changing a few SQL statements to return (or insert) the columns how you want them is much easier than changing the underlying table structure and having to make sure *all* of your queries then fit the new table structure. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] unsubscribe
---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] unsubscribe
please take me out from this group. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] unsubscribe
On Thursday 29 Sep 2005 11:47 am, [EMAIL PROTECTED] wrote: > please take me out from this group. ask the guy who put you in the group to do it -- regards kg http://www.livejournal.com/users/lawgon tally ho! http://avsap.org.in ಇಂಡ್ಲಿನಕ್ಸ வாழ்க! ---(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] Sending function parametars within EXECUTE ''SELECT...
On Wed, 2005-09-28 at 10:01 -0700, codeWarrior wrote: > > > > ---(end of broadcast)--- > > TIP 5: don't forget to increase your free space map settings > > > > FOR rec IN EXECUTE ''SELECT COUNT(col1) AS num FROM tmpTbl WHERE col2 = " || > aBid || '' LOOP > Huh! :) Seems so obvious now :) Is there a way to assing variable a value returned from query that accesses the temporary table in a way you explained above? For instance, I can do: myValue := col2 FROM tmpTbl WHERE someValue = somethingElse... But then I encounter the problem with temp tables. Can I do EXECUTE'd SELECT like you explained above? Or I need to do 'widdleing' with FOR like explained above? Mario ---(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] Sending function parametars within EXECUTE ''SELECT...
> Is there a way to assing variable a value returned from query that > accesses the temporary table in a way you explained above? > > For instance, I can do: > > myValue := col2 FROM tmpTbl WHERE someValue = somethingElse... If I'm parsing this correctly, all you need do is: SELECT col2 INTO myValue FROM ... Best regards, Ben Stewart -- Robert Bosch (Australia) Pty. Ltd. Engineering Quality Services, Student Software Engineer (RBAU/EQS4) Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA mailto:[EMAIL PROTECTED] http://www.bosch.com.au/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
