[SQL] NULL function arguments?
It seems that why I provide a NULL argument to a PL/pgSQL function
it makes the rest of the arguments NULL, too!
Consider this function:
CREATE FUNCTION callme(text, text) RETURNS boolean AS
'
BEGIN
RAISE NOTICE ''$1: %'', $1;
RAISE NOTICE ''$2: %'', $2;
RETURN TRUE;
END;
' LANGUAGE 'plpgsql';
So that when I try SELECT callme('hello', 'world');
I get back:
NOTICE: $1: hello
NOTICE: $2: world
But when I do SELECT callme('hello', NULL);
I get back:
NOTICE: $1:
NOTICE: $2:
I'm using Postgres 7.0. Possible bug?
Mark
Re: [SQL] Time Help
I'm not sure at all what you are asking, but I'm thinking you're trying to convert a "timespan" to a "time". Try adding it to a time like this: SELECT '0:00:00'::time + '02:10:06'::timespan; Mark "Brian C. Doyle" wrote: > > Hello all, > > I have a query result of @ 2 hours 10 mins 6 secs and I would like to > change that to 02:10:06. Currently the field is listed as "timespan" This > allows me to input date as 02:10:06 or 4h 10m 6s or even 2hrs 10 min 6 sec > which are all the formats that I will be entering the time formats! How do > I convert it into a the format of 02:10:06 > > Thanks to you all
Re: [SQL] Time Help
I tried it on a box with postgres 6.5.3 and I got the result you did. On postgres 7.0 I get 02:10:06 for both results. You should upgrade to 7.0 - it is _much_ better. Mark "Brian C. Doyle" wrote: > > Mark, > > I tried that and had to change it to: > > SELECT '0:00:00'::timespan + '02:10:06'::timespan; > > To get any response. the response i got was > > @ 2 hours 10 mins 6 secs > > Still in the wrong format > If is use : > > SELECT '0:00:00'::time + '02:10:06'::timespan; > > It get > > No such function 'time_timespan' with the specified attributes > > So i guess what I want to do is convert a timespan into time > How would I do that? > > At 09:55 AM 8/22/00 -0400, Mark Volpe wrote: > >I'm not sure at all what you are asking, but I'm thinking you're trying to > >convert a "timespan" to a "time". Try adding it to a time like this: > >SELECT '0:00:00'::time + '02:10:06'::timespan; > > > >Mark > > > >"Brian C. Doyle" wrote: > > > > > > Hello all, > > > > > > I have a query result of @ 2 hours 10 mins 6 secs and I would like to > > > change that to 02:10:06. Currently the field is listed as "timespan" This > > > allows me to input date as 02:10:06 or 4h 10m 6s or even 2hrs 10 min 6 sec > > > which are all the formats that I will be entering the time formats! How do > > > I convert it into a the format of 02:10:06 > > > > > > Thanks to you all
Re: [SQL] Time Help
SELECT '0:00:00'::time + '@ 2 hours 10 mins 6 secs'::timespan; ?column? -- 02:10:06 Mark "Brian C. Doyle" wrote: > > Mark, > > On your 7.0 box would you do: > > SELECT '0:00:00'::time + '@ 2 hours 10 mins 6 secs':timespan; > > For me and see if it will convert it! Need to decide if the upgrade will be > with it and if it does this then it is > > Thanks for your help Mark > > At 10:36 AM 8/22/00 -0400, you wrote: > >I tried it on a box with postgres 6.5.3 and I got the result you did. On > >postgres 7.0 I get 02:10:06 for both results. You should upgrade to 7.0 - it > >is _much_ better. > > > >Mark > > > >"Brian C. Doyle" wrote: > > > > > > Mark, > > > > > > I tried that and had to change it to: > > > > > > SELECT '0:00:00'::timespan + '02:10:06'::timespan; > > > > > > To get any response. the response i got was > > > > > > @ 2 hours 10 mins 6 secs > > > > > > Still in the wrong format > > > If is use : > > > > > > SELECT '0:00:00'::time + '02:10:06'::timespan; > > > > > > It get > > > > > > No such function 'time_timespan' with the specified attributes > > > > > > So i guess what I want to do is convert a timespan into time > > > How would I do that? > > > > > > At 09:55 AM 8/22/00 -0400, Mark Volpe wrote: > > > >I'm not sure at all what you are asking, but I'm thinking you're trying to > > > >convert a "timespan" to a "time". Try adding it to a time like this: > > > >SELECT '0:00:00'::time + '02:10:06'::timespan; > > > > > > > >Mark > > > > > > > >"Brian C. Doyle" wrote: > > > > > > > > > > Hello all, > > > > > > > > > > I have a query result of @ 2 hours 10 mins 6 secs and I would like to > > > > > change that to 02:10:06. Currently the field is listed as > > "timespan" This > > > > > allows me to input date as 02:10:06 or 4h 10m 6s or even 2hrs 10 > > min 6 sec > > > > > which are all the formats that I will be entering the time formats! > > How do > > > > > I convert it into a the format of 02:10:06 > > > > > > > > > > Thanks to you all
Re: [SQL] Multiple Index's
CREATE TABLE user_info(user_id name, entry_date date, info text); CREATE UNIQUE INDEX user_info_key ON user_info(user_id, entry_date); "Brian C. Doyle" wrote: > > Hello all, > > How would I prevent a user from submitting information to a table once they > have already done so for that day. I would need them to be able > information on future dates as well as have information in the table from > past dates from that user. > > I am looking for something like insert user_id, date, info where user_id > and date are not the same... does that make sense? > > Brian C. Doyle
[SQL] Typecast a user-defined type?
Hi, I've created my own datatype for Postgres. I have found it necessary to be able to convert it to text, so I could match it up with a text column in a UNION. I figured Postgres would do this for me, but then, I'm naive. Can someone give me a hint, or point me to the appropriate material on how to get "value::text" to work with my new type? Thanks, Mark
[SQL] AFTER triggers, short question
If I create a trigger that runs AFTER a DELETE, and then I delete several rows, will the trigger function see the same thing every time it's called, namely that all the rows I deleted are actually gone? Mark
Re: [SQL] dynamic object creation
You may want to think about creating your table like this (for example): CREATE TABLE data ( key text, field_type char, value text ); CREATE UNIQUE INDEX data_key ON data(key, field_type, value); So this way each "record" takes up several rows in the table, and each "field" can take up as many rows as you need. A table like this, with two columns being arrays: key | field1 | field2 - a| [x,y,z] | [a,d,f] b| [m,n] | (NULL) Can be represented like this instead: key | field_type | value - a| 1 | x a| 1 | y a| 1 | z a| 2 | a a| 2 | d a| 2 | f b| 1 | m b| 1 | n I'm not sure what your data looks like, but I hope this helps. Mark Indraneel Majumdar wrote: > > Hi, > > I'm not sure if the subject line has been proper. I have this following > problem which I hope PostgreSQL can handle. > > I'm converting a complex flatfile where records are arranged serially. > some fields are as 'n' times repeating blocks of multiple lines. Some > subfields within these are also 'n' time repeating blocks of multiple > lines. So in my main table I do not know (until at run time) how many > fields to create (same for any sub tables). How can I do this dynamically? > > I tried using arrays, but retrieval from that is causing some problems. I > have already checked the array utilities in the contrib section and have > extended the operator list for other types (I'll send the file to it's > original author so that he may include it if he wishes). > > I think there must be some object-oriented way of doing this without > creating too many keys. or are keys the only and best method? Using this > is causing a performance hit. If it's any help, what I'm trying to convert > are biological databases distributed in 'SRS' flatfile format from > ftp.ebi.ac.uk/pub/databases/ > > Thank you, > Indraneel > > /. > # Indraneel Majumdar ¡ E-mail: [EMAIL PROTECTED] # > # Bioinformatics Unit (EMBNET node), ¡ URL: http://scorpius.iwarp.com # > # Centre for DNA Fingerprinting and Diagnostics, # > # Hyderabad, India - 500076 # > `/
Re: [SQL] Re: NULL
\N is normally used to represent NULL in a text file, however you can change that to another string (or an empty string) using COPY FROM ... WITH NULL AS Mark Sandis Jerics wrote: > > Hello, > > how must i write a NULL value in a text file for the \copy command to > understand it? > NULL, \0 doesnt work.. > > how must i write a boolean value in a text file for the \copy command to > understand it? > t doesnt work, however in a file written by /copy command it looks as > t or f .. > > tried to search archives, but it seems deadly slow... > > Thanks in advance, > sandis
[SQL] BTP_CHAIN errors fixed?
Hi, I have been using PostgreSQL-7.0.0 and have had the problem that, when searching a btree index that contains large numbers of duplicate keys, Postgres crashes with a BTP_CHAIN error. Now that I have installed 7.1beta3 the problem has seemingly been fixed. Was this problem actually fixed somewhere between 7.0.0 and 7.1beta3, or am I just getting lucky right now (just want to know before I put this into production :-))? Thanks, Mark
Re: [SQL] BTP_CHAIN errors fixed?
Tom Lane wrote: > > Mark Volpe <[EMAIL PROTECTED]> writes: > > I have been using PostgreSQL-7.0.0 and have had the problem that, when > > searching a btree index that contains large numbers of duplicate keys, > > Postgres crashes with a BTP_CHAIN error. Now that I have installed 7.1beta3 > > the problem has seemingly been fixed. Was this problem actually fixed > > somewhere between 7.0.0 and 7.1beta3, or am I just getting lucky right now > > Well, it was worked on ;-) ... that whole chunk of code was rewritten. > Whether it has new bugs remains to be seen, but the old bugs are gone... > > regards, tom lane Thanks for the quick response. The new code will get plenty of testing from me!
Re: [SQL] interval query.
Try SELECT * FROM Towns WHERE id= OR id BETWEEN 3 AND 12 Antti Linno wrote: > > Good morning. > > Is there some way to make interval query? > > Towns table(estonia towns, heh :P) > > id | name > > 1 Elva > 2 Tartu > Tallinn > 3 Tallinn/Haabersti > 4 Tallinn/Mustamae > ... > etc. > > What I need is when the town's id= I want to make query > where id= OR id=[3..12] for example. I could generate bunch of OR's > like id=3 OR id=4 ... but is there some more elegant solution? > > Greetings, > Antti
Re: [SQL] Permissions for foreign keys
The problem is fixed in the 7.1 beta series. Rick Delaney wrote: > > I'm using 7.0 and have noticed that I need to grant SELECT and UPDATE > permissions on any referentially-related tables. Can/should I get > around this? A somewhat contrived example: > > CREATE TABLE emp ( > id integer PRIMARY KEY, > salary integer > ); > CREATE TABLE proj ( > id integer PRIMARY KEY, > emp_id integer REFERENCES emp > ); > CREATE TABLE bill ( > id integer PRIMARY KEY, > proj_id integer REFERENCES proj > ); > INSERT INTO emp VALUES (1, 10); > INSERT INTO proj VALUES (1, 1); > INSERT INTO bill VALUES (1, 1); > > GRANT ALL ON proj TO someone; > > Connect as someone: > => INSERT INTO proj VALUES (2, 1); > ERROR: emp: Permission denied. > => UPDATE proj SET id = 2; > ERROR: bill: Permission denied. > > It appears that I need to grant: >SELECT,UPDATE on emp to UPDATE or INSERT into proj. >SELECT,UPDATE on bill to UPDATE proj. > > When I grant these permissions, the above statements succeed. > > If I don't want users to have UPDATE (or even SELECT) access on the > other tables (bill and emp), but I want referential integrity, what can > I do? > > -- > Rick Delaney
Re: [SQL] Index scan
Since you are selecting all the rows of media, there is no reason to use the index to do this as it would just slow things down. Mark Najm Hashmi wrote: > > Hi all, > I am unable to understand why my inidcies are not used in the query. > I have following indices: > index on categories.root > index on media.category > unique index on categories.id > Here is my query : > mondo=# explain select m.id >form media m, categories c > where c.id=m.category and c.root like 'B%'; > NOTICE: QUERY PLAN: > > Hash Join (cost=22.55..116.80 rows=11 width=28) > -> Seq Scan on media m (cost=0.00..60.40 rows=840 width=16) > -> Hash (cost=22.50..22.50 rows=20 width=12) > -> Index Scan using cat_id_root on categories c > (cost=0.00..22.50 rows=20 width=12) > EXPLAIN > > I simply dont understand why it uses seq scan on media. > Thanks all for your help. > -Najm
Re: [SQL] SQL (table transposition)
Hope you like black magic :) SELECT IND AS T1_INDEX, MIN(CASE WHEN KEY=1 THEN VALUE ELSE NULL END) AS KEY1VAL, MIN(CASE WHEN KEY=2 THEN VALUE ELSE NULL END) AS KEY2VAL, MIN(CASE WHEN KEY=3 THEN VALUE ELSE NULL END) AS KEY3VAL FROM T2 GROUP BY IND ORDER BY IND; Mark [EMAIL PROTECTED] wrote: > > Is there any way to get table T3 (below) from T1 and T2 using SQL (select, view, >etc)? > > T3 is basically all INDEX values from T1 matched to IND from T2 with the >corresponding KEY/VALUE pairs transposed from rows to columns. > > --- > |INDEX| (T1) > --- > | 1 | > | 2 | > | 3 | > --- > > - > |IND|KEY| VALUE | (T2) > - > | 1 | 1 | val_a | > | 1 | 2 | val_b | > | 1 | 3 | val_c | > | 2 | 1 | val_d | > | 2 | 2 | val_e | > | 3 | 1 | val_f | > | 3 | 3 | val_g | > - > > -- > |T1_INDEX|KEY1VAL|KEY2VAL|KEY3VAL| (T3) > -- > | 1| val_a | val_b | val_c | > | 2| val_d | val_e | | > | 3| val_f | | val_g | > -- > > Thanks for any suggestions > > med vänlig hälsning > /Dana
[SQL] Re: PL/pgSQL
Oh yeah, I'm using v7.0 Mark
[SQL] PL/pgSQL evaluation order
Hi, I have trigger function with the statement: IF TG_OP=''INSERT'' OR NEW.ip!=OLD.ip THEN expecting the TG_OP=''INSERT'' to be evaluated first, and if true, proceed with the body. But it gets evaluated as the result of an INSERT statement, I get a (rather strangly worded) error: ERROR: record old is unassigned yet which means that the second statement is still being evaluated even though the first is true. Assuming that this is the result of the parser/planner messing with that statement, is there a way to force evaluation order without having to go: IF TG_OP=''INSERT'' THEN ELSE IF NEW.OP!=OLD.ip THEN END IF; END IF; Thanks, Mark
Re: [SQL] PL/pgSQL evaluation order
Doesn't seem like a bad idea. I did find a better workaround, though - cond:=TG_OP=''INSERT''; IF cond=FALSE THEN cond:=NEW.ip!=OLD.ip; END IF; IF cond=TRUE ... so it won't kill me. Anyway, I've been totally impressed with the 7.0 release. I'm building the EPA IP address registration system on top of it. My favorite feature is the support for REFERENCES (though I had to hack the code a bit to get around a permissions problem). Thanks, Mark Jan Wieck wrote: > > Maybe we should change the treatment of unassigned rowtype > values that any reference to it holds the NULL value? >
Re: [SQL] foreign key take too much time to check
Try creating the tables without the constraint first, then populate them, and then add the foreign key constaint as the last step using ALTER TABLE/ADD CONSTRAINT. Mark Jie Liang wrote: > > Hi, there, > > I want add a constraint to my tables: > I have 2 tables: > 1. Table_A(id int 4 primary key, item text, ..), > 2. Table_B(id int4, cid int4, constraint b_fk foreign key (id) > refereneces Table_A(id) > on delete cascade on update cascade); > Table_A has ~900,000 rows, > Table_B has ~1,200,000 rows. > I tried serveral ways to generate the b_fk > 1. alter table add constraint b_fk foreign key (id) refereneces > Table_A(id) > on delete cascade on update cascade, > 2. pg_dump -d -t Table_B -f B dbname, then psql dbname < B > even before that , I create an index for id of Table_B, and vacuum table > Table_A and Table_B, > > insert (check) speed is very slow, ~100 rows per minute, for my table > has ~1.2M rows will > take more than 200hours, I wonder to compare 2 integers why takes so > long, if no b_fk , the Table_B > can be reload in 5 minutes... > > Is anybody know how reslove this problem? I 'll appreciate. > > -- > Jie LIANG > > Internet Products Inc. > > 10350 Science Center Drive > Suite 100, San Diego, CA 92121 > Office:(858)320-4873 > > [EMAIL PROTECTED] > www.ipinc.com
[SQL] Rules aren't doing what I expect
Hi again, I have a table with a trigger that can potentially modify a row before it gets inserted or updated: CREATE TABLE t1 (a int); CREATE FUNCTION t1_validate() RETURNS opaque AS ' BEGIN IF (NEW.a>10) THEN NEW.a=10; END IF; IF (NEW.a<0) THEN NEW.a=0; END IF; RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER t1_trig BEFORE INSERT OR UPDATE ON t1 FOR EACH ROW EXECUTE PROCEDURE t1_validate(); I have another table that tracks changes in the first table with rules: CREATE TABLE t1_log (old_a int, new_a int); CREATE RULE t1_insert AS ON INSERT TO t1 DO INSERT INTO t1_log VALUES(NULL, NEW.a); CREATE RULE t1_update AS ON UPDATE TO t1 DO INSERT INTO t1_log VALUES(OLD.a, NEW.a); CREATE RULE t1_delete AS ON DELETE TO t1 DO INSERT INTO t1_log VALUES(OLD.a, NULL); When I try this out, however, the rule seems to use the original value, rather than the "corrected" value. INSERT INTO t1 VALUES(2); INSERT INTO t1 VALUES(8); INSERT INTO t1 VALUES(15); SELECT * FROM t1; a 2 8 10 The table t1 shows the corrected value of 10, but, SELECT * FROM FROM t1_log; old_a | new_a ---+--- | 2 | 8 |15 The t1_log table doesn't show what was actually inserted into t1! Are there any changes I can make to the logic above so that t1_log can show the correct value? Thanks, Mark
Re: [SQL] Rules aren't doing what I expect
The actual trigger function I'm working with is over two screens long and rather expensive to be calling twice! Perhaps I need to add another trigger that updates the log table with the correct values after the fact. Recursive triggers, fun! Thanks for the help, Mark Ang Chin Han wrote: > > Either somehow rewrite your trigger as a rule, or stick another > trigger to change the value before getting into your log table. > You might be able to reuse your trigger function, I think, just > point the trigger to that function. A bit inefficient, since it > gets called twice.
Re: [SQL] Rules aren't doing what I expect
Tom Lane wrote: > > Queries added by non-INSTEAD rules are always performed before the > initially-given query, so you're right, the rule will see the unmodified > value. > > I'd suggest folding the log-entry-making into your trigger, actually. > If you have a trigger anyway then the insert into t1_log is only one > more line in that trigger procedure... > > regards, tom lane Thanks for the explanation, Tom. I left out part of my story though. :) I would like normal users to be able to modify t1 but not t1_log, and doing what you said would require INSERT permission on t1_log. So what I did was go ahead and allow INSERT permission, but create before and after triggers on t1_log that check the inserted values against reality... but that brings up another question - If I do an UPDATE on t1, it calls a trigger which eventually does: INSERT INTO t1_log VALUES(OLD.a, NEW.a); If t1_log has before and after triggers, the before trigger will always see the old row in t1, and the after trigger will always see the new data, right? At least that's what I'm seeing. The "visibility of data changes" document was kinda confusing... Mark
Re: [SQL] select an entry with a NULL date field
The syntax you're looking for is: SELECT entry_id FROM tbl_date WHERE date_02 IS NULL; Mark Web Manager wrote: > > Hello, > > I have a problem with PostgreSQL when I try to select or delete an entry > with an empty date. That's a typical entry > > Table tbl_date > - > entry_id154 > date_012000-01-15 > date_02this date is NULL > namemy_test > - > > I want to select every entry containing date_02 as NULL > I tried : > >select entry_id from tbl_date where date_02=NULL; > ERROR: parser: parse error at or near "null" > >select entry_id from tbl_date where date_02=''; > Not work, that's a wrong date format > >select entry_id from tbl_date where date_02=""; > Not work, "" considered as an attribute > > Thank you for your time! > -- > ~ > Marc Andre Paquin
