[SQL] Need info: pl/pgsql performance
Hi, I would to know if there is any articles or books that talk about pl/pgsql performance especially versus C. Thanking you in advance for your help. Regards. Najm
[SQL] Need help! parse erro
Hi every one, I have just strated to use postgres. I wrote a vert
simple pl/pgsql function as given below:
CREATE FUNCTION
add_new_user(varchar(32),char(8),varchar(128),varchar(128),varchar(32),
varchar(32)) RETURNS boolean AS' DECLARE
oldUser RECORD;
USR ALIAS FOR $1;
PWORD ALIAS FOR $2;
EMAIL ALIAS FOR $3;
ADDR ALIAS FOR $4;
CITY ALIAS FOR $5;
CNTRY ALIAS FOR $6;
BEGIN
SELECT INTO oldUser *
FROM users
where username=USR AND password= PWORD;
IF FOUND
THEN
RETURN ''f'';
ELSE
INSERT INTO USERS(username,password,email,address,city,country)
values(USR,PWORD,EMAIL,ADDR,CITY,CNTRY);
RETURN ''t'';
END IF;
END;'
LANGUAGE 'plpgsql';
When I run it I get the following error:
najdb=# select
add_new_user('najm'::varchar,'najm1234'::char,'najmh@whatever'::varchar,'123street'::varchar,'mtl'::varchar,'canada'::varchar);
ERROR: parser: parse error at or near "$1"
Someone, please help me out here.
I would also appreciate if you all "wizards" out there could send me
some more complex examples of plpgsql functions and triggers sinece
postgres docs doesn't have any good ones. Thank you all for all your
help.
Regards.
Najm.
Re: [SQL] INSERT [IGNORE] INTO TABLE
Edmar Wiggers wrote: > > If I try to insert some row which in the table already exist the > > postgre don't insert it and tell some error. Everything ok. > > I'll insert only if there is not the same (same key) record, > > else don't insert and don't tell me errors about it. > > In MySQL is a switch [IGNORE]. > > Not a good feature for me. What do you do if the record already exists? > Update it? > > Check existence and then insert or update. If you want, I guess you could > wrap that inside a stored procedure. Hi, here is an example of using function using pl/pgsql for inserting and checking whether an instance exists or not. CREATE FUNCTION add_new_user(text,text) RETURNS bool AS' DECLARE oldUser RECORD; USR ALIAS FOR $1; PWORD ALIAS FOR $2; BEGIN SELECT INTO oldUser * FROM users where username=USR AND password= PWORD; IF FOUND THEN RETURN ''f''; ELSE INSERT INTO USERS(username,password) values(USR,PWORD); RETURN ''t''; END IF; END;' LANGUAGE 'plpgsql'; Regards. Najm
Re: [SQL] Re: Returning Recordsets from Stored-procs
Andreas Tille wrote: > On Mon, 6 Nov 2000, Grant Finnemore wrote: > > > Whilst this is an unfortunate position at the moment, it has been my experience >that it does not cause insurmountable problems. > > (Some short term headaches - yes. ;-) > After learning this as a fact you have short term headaches but before > you have continuos headache while trying to port a database. > I think I'm not the only one who would be really, really happy if > *real* stored procedures would be high on top of the todo list. > > (In fact this is the *only* thing I'm currently really missing in > PostgreSQL.) > > Kind regards > > Andreas. I will second Andreas Najm
[SQL] [sql]Joins
I am facing a dilemma at my work, I am using postgres first time. For some reason, my co-workers think that in Postgres joins i.e. simple joins of two or three tables are so bad that their cost is exponential. They believe that postgres simply takes Cartesian product of joining table in order to obtain desired tuples i.e. no optimization is done. I refused to believe so. I need your help to convince them that it is okay to join tables --two or three tables :)-- so we can eliminate redundancies from the database. I also want to know how postgres optimizes a join query. Thank you very much your help. Regards Najm
[SQL] Cache look up failure
Hi All, I am trying to insert a tuple in the tuple, and i am getting
the follwoing error message:
fliprdb=# insert into collection(name, artist_id) values('El Baile
Aleman',2);
ERROR: fmgr_info: function 24011: cache lookup failed
Can someone help me out here. Thnaks in advance for your help.
Najm
[SQL] Cache lookup failure
Hi All,
I am trying to do a simple insert, and I am getting cache look failure
error.
Could someone explain what causing it? My query and error are given
below:
fliprdb=# insert into collection(name, artist_id) values('El Baile
Aleman',2);
ERROR: fmgr_info: function 24011: cache lookup failed
Thanks in advance for all your help
Regards
Najm
[SQL] pg_dump Error
Hi all, while trying to dump db shcema and content, I am getting the following error: [najm@localhost najm]$ pg_dump -D fliprdb > flipr.sql PQgetvalue: ERROR! tuple number 0 is out of range 0..-1 Segmentation fault (core dumped) Any suggestion what I am doing wrong and why i am getting this error. Thank you all in advance for your help Regards. Najm
[SQL] About Index
Hi, I have created a table songs as follwoing: CREATE TABLE songs ( song_id serial NOT NULL, title character varying(50) NOT NULL, composer varchar(50), performer varchar(50), artist_id int4 not null, file_size int4, description text, extrInfo text, highmd5 text, lowmd5 text, PRIMARY KEY (song_id), constraint fk_songs_art foreign key( artist_id) references artist ); I also created an other index on attribute title as: create unique index title_idx on songs(title); Then I vacuum analyzed my data base. On command line I type : explain select title from songs where song_id between 1 and 15 order by song_id; NOTICE: QUERY PLAN: Sort (cost=1.70..1.70 rows=14 width=16) -> Seq Scan on songs (cost=0.00..1.44 rows=14 width=16) EXPLAIN One thing I don't uderstand why query planer is using a seq scan on my table. Why it is not using indexing defined. Thank you all for all your help. Regards. Najm
[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
[SQL] PostGreSql7.1 beta version
Hi all, I am just wondering where can i find the link to down load PostGreSql7.1 beta versoin . I heard it is very stable my be I can use it see how many times I tumble on bugs I don't do it intentionally but it happens to me all the time :)... Regards. Najm
[SQL] SQL funtion
Hi All, I have written a very simply function as follows: create function song_info(int4) returns setof songs as' select songs.* from songs where $1=song_id' language 'sql'; when iIcall it I get the following:flipr=# select song_info(4); ?column? --- 136428992 (1 row) Is not it suppose to retrun me the tuple form songs table rather than oid If I want to get a complete tuple what can I do. Thank you all for your help. Regards, Najm.
[SQL] pl/pgsql Limits
Hi All, As it is known that any funtion, written in pl/pgsql, can only retrun one tuple. I am just wondering it were true as well for function written in C language. I need to write few function that will retrun mulitiple rows satsifying a certain set of conditions. Where I can get some examples. Tahnks in advance. Najm
[SQL] Selecting Current value from a sequence
Hi all, It is a very simple but I am not able to recall how to do it I just need to find out the current value of a seq. It is very simple select statement but I can't recall it... Help me please before I get myself get fired -:). Regards, Najm
Re: [SQL] Selecting Current value from a sequence
Thank you all for your help. Najm
[SQL] Correct Syntax for alter table ..add constraint
Hi All, What is the correct syntax for adding a foreign key constraint from the command line. I am using v7.1 beta3. I am doing the follwoing: alter table users add constraint age_fk foreign key(age) references age_list(id); And I get the following error: flipr=# alter table users flipr-# add constraint age_fk foreign key(age) references age_list(id); NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: referential integrity violation - key referenced from users not found in age_list I have define attribute id as primary key of the table... Thanks for your help. Regards,Najm.
Re: [SQL] Correct Syntax for alter table ..add constraint
Josh Berkus wrote: > Najm, > > > references age_list(id); > > And I get the following error: > > flipr=# alter table users > > flipr-# add constraint age_fk foreign key(age) references > > age_list(id); > > NOTICE: ALTER TABLE ... ADD CONSTRAINT will create > > implicit trigger(s) > > for FOREIGN KEY check(s) > > ERROR: referential integrity violation - key > > referenced from > > users not found in age_list > > Simple ... you have values in the AGE column that are not in > the age_list table. Thus you're in violation of the foriegn > key you're trying to establish. > > -Josh Berkus HI Josh, All values in age column of are null. I have no value there.. and I have specified to accetp nulls i.e. I dont have constraint not null on this particular column... Najm
Re: [SQL] How to change the ownership of the table?
> > > Working out the exact UPDATE command to use is left as an exercise > for the student ;-) > > regards, tom lane It is funny.. LOL Najm
Re: [SQL] Permissions for foreign keys
Hey Rick, I am sure there are more elegant solutions but I have a simple one. Write a trigger that will grant the permissions before insert or update and and revoke all privileges after the insert or update. -Najm 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
[SQL] Index scan
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
[SQL] pgsql and cursor
Hi all, I am not sure If it is allowed to use cursor inside pgsql functions. If it is possible, please someone could send exact synatx how it is used. If it is not allowed is there a way arround it? I need to do some calculations and then return this value as text. Thanks in advance for all your help. Najm
[SQL] What's wrong with this function
Hi all, Here is a plpgsql function: flipr'#create function test_cur() returns text as' flipr'# declare flipr'# mycur cursor for select title from songs where song_id=10; flipr'# usrrecord; flipr'# resultstext; flipr'# begin flipr'# open mycur; flipr'# fetch next from mycur into usr; flipr'# close mycur; flipr'# results:= usr.title; flipr'# flipr'# end; flipr'# ' language 'plpgsql'; CREATE flipr=# select test_cur() as Title; NOTICE: plpgsql: ERROR during compile of test_cur near line 2 ERROR: parse error at or near "cursor" What I am doing wrong? Thanks in advance for your help. Regards, Najm
Re: [SQL] What's wrong with this function
Jie Liang wrote: > I just know you can use implict cursor inside the plpgsql > e.g > declare result text; tcount int4; > > rec record; > begin > FOR rec IN select_clause LOOP > statements > END LOOP; > end; > Thank you Jie for your help. I am bit confused about how it works. I want for each row , obtained by select statment, get certain values and then do some calculations and out put that resulst eg for rec IN select title, dcount from songs where artist='xyz' tcount:= tcount+rec.dcount; END LOOP; return tcount; would this work ? Thanks again for your help. Regards, Najm
[SQL] String Concatnation
Hi, How can I concatnate two varialbles, seperated by a |, that are type text together? v, v1 text; some work then res:= v ||''|''|| v1; this syntex does not work in plpgsql?? Any ideas how to do it ??? Thanks. Najm
[SQL] Wierd postgres Problem
Hi All, I am trying to define a new set of tables an I am getting this strange syntex problem on date, or timestamp data types. I am also getting error on not null constranit as well... Postgres is behaving strangely first definations with not null cmdb=# create table media_received ( cmdb(# comp_id not null, cmdb(# dept_id not null, cmdb(# date_rec timestamp default 'now', cmdb(# units int4 default 0, cmdb(# media_type varchar(64), cmdb(# enqued int4 check (enqued=
[SQL] Controlling Reuslts with Limit
Hi, I was reading through Bruce's on line . I found follwing bit unclear... "Notice that each query uses ORDER BY . Although this clause is not required, LIMIT without ORDER BY returns random rows from the query, which would be useless. " When I run a query several time I get the same results as given flipr=# select song_id from songs limit 5; song_id - 945 946 947 948 949 (5 rows) flipr=# select song_id from songs limit 5; song_id - 945 946 947 948 949 (5 rows) flipr=# select song_id from songs limit 5; song_id - 945 946 947 948 949 (5 rows) flipr=# select song_id from songs limit 5; song_id - 945 946 947 948 949 (5 rows) flipr=# select song_id from songs limit 5; song_id - 945 946 947 948 949 (5 rows) flipr=# select song_id from songs limit 5; song_id - 945 946 947 948 949 I just want to know what exatly --"LIMIT without ORDER BY returns random rows from the query" --means Regards begin:vcard n:Hashmi;Najm x-mozilla-html:FALSE org:Mondo-Live.com;www.flipr.com adr:;; version:2.1 email;internet:[EMAIL PROTECTED] x-mozilla-cpt:;6144 fn:Najm Hashmi end:vcard
[SQL] quotes in pl/pgsql
Hi all, I just want to know how to put quotes around a string. Is there a function to do so? If not how can I escape a single quote. Thank you in advance. begin:vcard n:Hashmi;Najm x-mozilla-html:FALSE org:Mondo-Live.com;www.flipr.com adr:;; version:2.1 email;internet:[EMAIL PROTECTED] x-mozilla-cpt:;6144 fn:Najm Hashmi end:vcard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] quotes in pl/pgsql 0n variable type text or varchar
Roberto Mello wrote: > On Thu, Mar 08, 2001 at 05:54:38PM -0500, Najm Hashmi wrote: > > Hi all, I just want to know how to put quotes around a string. Is there a > > function to do so? > > If not how can I escape a single quote. > > Others have answered how to quote a single string. I wrote an entire > section on quotes on the PL/SQL-to-PL/pgSQL-HOWTO. It's called "Quote me > on that" and mentions several cases on quote usage in PL/pgSQL and what to > do about it. > http://www.brasileiro.net/roberto/howto > > -Roberto > > -- > +| http://fslc.usu.edu USU Free Software & GNU/Linux Club|--+ > Roberto Mello - Computer Science, USU - http://www.brasileiro.net > http://www.sdl.usu.edu - Space Dynamics Lab, Web Developer > DOS = Damned Old Software Hi, I just want to know is there way to put single quotes around the value of var type text or varchar. Thanks in advance. Regards. begin:vcard n:Hashmi;Najm x-mozilla-html:FALSE org:Mondo-Live.com;www.flipr.com adr:;; version:2.1 email;internet:[EMAIL PROTECTED] x-mozilla-cpt:;6144 fn:Najm Hashmi end:vcard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] from PosgreSQL 7.1b3 to 7.0.3
I have PosgreSQL 7.1b3 running on one of our test servers. It seems like PosgreSQL 7.1b3 is not very stable. I want to go back to 7.0.3v since it it the most stable version available. I am just wondering what should I do. can I reinstall 7.0.3 on 7.1b3 directly ? If not then what steps should I take. I can always use pg_dump to dump data and stuff.. By the way 7.1b3 is crashing 3 to 4 times a week. We have 7.0.3 running on another server and it rarely crashes. Thanks in advance. Regards. begin:vcard n:Hashmi;Najm x-mozilla-html:FALSE org:Mondo-Live.com;www.flipr.com adr:;; version:2.1 email;internet:[EMAIL PROTECTED] x-mozilla-cpt:;6144 fn:Najm Hashmi end:vcard ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Max Conncection limit?
We start a server and initiate 30 connections(with the libpq C interface) which are stored in a stack to use and and to be reused. After awhile I try to start another server that will also try to initiate 30 connections, for each connection I then get a PQErrorMessage like that : "Sorry, too many connections" if I do a ps -ax at that time I get a lot of /usr/bin/postgres 192.168.0.1 user name passwd idle what I usually do is restart postgres but I wish I didnt have to do that... That is not an elegant solutuion any ways. What can I do? Thanks in advance. Regards begin:vcard n:Hashmi;Najm x-mozilla-html:FALSE org:Mondo-Live.com;www.flipr.com adr:;; version:2.1 email;internet:[EMAIL PROTECTED] x-mozilla-cpt:;6144 fn:Najm Hashmi end:vcard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Postgres & XML
Hi all, I was just wondering if there is way or some sort of utility to incorporate XML in postgrres. Thanks in advance. begin:vcard n:Hashmi;Najm x-mozilla-html:FALSE org:Mondo-Live.com;www.flipr.com adr:;; version:2.1 email;internet:[EMAIL PROTECTED] x-mozilla-cpt:;6144 fn:Najm Hashmi end:vcard ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] is it me or trigger side effects
Hi all,
I have written a trigger to update a table called categories whenever
a tuple is either deleted or inserted in tables articles, media, and links.
It works fine for inserts but "pukes" on deletes :). I am using two auxiliary
addcount(varchar) and delecount (varchar)
I am getting the following error on deletes:
record new is unassigned yet .
below is my code.
drop function addCount(varchar);
create function AddCount(varchar) returns integer as '
declare
cat_id alias for $1;
len integer;
cnt integer;
cond varchar;
begin
len:= length(cat_id);
cnt:=0;
for i in 1 .. len loop
cnt:=cnt +1;
cond:=substr(cat_id,1,cnt);
update categories
set items= items+1 where id like cond;
end loop;
return cnt;
end;
' language 'plpgsql';
select addCount('KDA');
drop function delCount(varchar);
create function delCount(varchar) returns integer as '
declare
cat_id alias for $1;
len integer;
cnt integer;
cond varchar;
begin
len:= length(cat_id);
cnt:=0;
for i in 1 .. len loop
cnt:=cnt
+1;
cond:=substr(cat_id,1,cnt);
update categories
set items= items-1 where id like cond;
end loop;
return cnt;
end;
' language 'plpgsql';
select delCount('KDA');
drop trigger trigger_update_articles on articles;
drop trigger trigger_update_links on links;
drop trigger trigger_update_media on media;
drop function updateCat();
create function updateCat() returns opaque as '
declare
rec record;
rename new to cat;
rename old to ct;
maxlen integer;
begin
if tg_op = ''INSERT'' and cat.category
is null then
raise exception
''You are missing entry for category field'';
else
if cat.display
then
maxlen:= addCount(cat.category);
return cat;
else
return cat;
end if;
end if;
if tg_op = ''DELETE''then
maxlen:= delCount(ct.category);
return ct;
end if;
end;
' language 'plpgsql';
create trigger trigger_update_articles before insert or delete
on articles for each row execute procedure updateCat();
create trigger trigger_update_media before insert or delete
on media for each row execute procedure updateCat();
create trigger trigger_update_links before insert or delete
on links for each row execute procedure updateCat();
begin:vcard
n:Hashmi;Najm
x-mozilla-html:FALSE
org:Mondo-Live.com;www.flipr.com
adr:;;
version:2.1
email;internet:[EMAIL PROTECTED]
x-mozilla-cpt:;6144
fn:Najm Hashmi
end:vcard
---(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] pg_dump error plus RelationBuildTriggers: 1 record(s) not found for rel links
Hi all, here is error I that am getting: pg_dump -s mondo > mondo.out PQgetvalue: ERROR! tuple number 0 is out of range 0..-1 I faced similar example in the past too. At that time, there was a referentional constraint problem but this situation is different. I am usning postgres 7.0.3 on redhat 6.2 Thanks in advance for your help. Regards begin:vcard n:Hashmi;Najm x-mozilla-html:FALSE org:Mondo-Live.com;www.flipr.com adr:;; version:2.1 email;internet:[EMAIL PROTECTED] x-mozilla-cpt:;6144 fn:Najm Hashmi end:vcard ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Error on Drop table
Hi, I am trying to drop a table and I get the following error: drop TABLE links; ERROR: RelationBuildTriggers: 1 record(s) not found for rel links mondo=# Could someone explain this to me. I would really appreciate the help. begin:vcard n:Hashmi;Najm x-mozilla-html:FALSE org:Mondo-Live.com;www.flipr.com adr:;; version:2.1 email;internet:[EMAIL PROTECTED] x-mozilla-cpt:;6144 fn:Najm Hashmi end:vcard ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] TypeCreate: type links already defined
HI all, I just posted a message about droping a table. I droped it using pgaccess from the pg_class table. Now I want to recreate the table and I get the following message: TypeCreate: type links already defined I need to fix this problem in hurry please someone could reply to it. Thanks alot in advance for your help My guess is Tom is not checking his email today :) Regards begin:vcard n:Hashmi;Najm x-mozilla-html:FALSE org:Mondo-Live.com;www.flipr.com adr:;; version:2.1 email;internet:[EMAIL PROTECTED] x-mozilla-cpt:;6144 fn:Najm Hashmi end:vcard ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Error:TypeCreate: type links already defined
Hi all, I have posted my question since last Thurday and noone has answered it yet. My problems is that I droped a table using pgaccess from pg_class. Now If I try to create that table I get the following error: TypeCreate: type links already defined. Could some one help me out here. Regards begin:vcard n:Hashmi;Najm x-mozilla-html:FALSE org:Mondo-Live.com;www.flipr.com adr:;; version:2.1 email;internet:[EMAIL PROTECTED] x-mozilla-cpt:;6144 fn:Najm Hashmi end:vcard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] \i command
Hi All, From pgsql, I try to insert data in table using the \i command. Nothing takes place and after this command pgsql is hung... i cant use it any more. The same command works with all other tables but this is only one I am having problem with. I have attached my file with message. Could someone help me out here what is the reason for this behaviour. I am using postgres7.03 on RH 6.2. CREATE TABLE "tracks" ( "song_id" int4, "coll_id" int4, "track_no" int4 ); INSERT INTO "tracks" VALUES (1,2,1); INSERT INTO "tracks" VALUES (2,2,2); INSERT INTO "tracks" VALUES (3,2,3); INSERT INTO "tracks" VALUES (4,2,4); INSERT INTO "tracks" VALUES (5,2,5); INSERT INTO "tracks" VALUES (6,2,6); INSERT INTO "tracks" VALUES (7,2,7); INSERT INTO "tracks" VALUES (8,2,8); INSERT INTO "tracks" VALUES (9,2,9); INSERT INTO "tracks" VALUES (10,2,10); INSERT INTO "tracks" VALUES (11,58,12); INSERT INTO "tracks" VALUES (12,58,1); INSERT INTO "tracks" VALUES (13,3,12); INSERT INTO "tracks" VALUES (14,59,3); INSERT INTO "tracks" VALUES (15,61,2); INSERT INTO "tracks" VALUES (16,61,5); INSERT INTO "tracks" VALUES (17,3,6); INSERT INTO "tracks" VALUES (18,59,1); INSERT INTO "tracks" VALUES (19,61,4); INSERT INTO "tracks" VALUES (20,3,4); INSERT INTO "tracks" VALUES (21,59,2); INSERT INTO "tracks" VALUES (22,55,10); INSERT INTO "tracks" VALUES (24,4,3); INSERT INTO "tracks" VALUES (25,4,7); INSERT INTO "tracks" VALUES (26,4,1); INSERT INTO "tracks" VALUES (27,4,10); INSERT INTO "tracks" VALUES (28,5,8); INSERT INTO "tracks" VALUES (29,5,1); INSERT INTO "tracks" VALUES (30,5,NULL); INSERT INTO "tracks" VALUES (31,6,1); INSERT INTO "tracks" VALUES (32,39,1); INSERT INTO "tracks" VALUES (33,39,2); INSERT INTO "tracks" VALUES (34,39,3); INSERT INTO "tracks" VALUES (35,39,4); INSERT INTO "tracks" VALUES (36,39,5); INSERT INTO "tracks" VALUES (37,39,6); INSERT INTO "tracks" VALUES (38,39,7); INSERT INTO "tracks" VALUES (39,39,8); INSERT INTO "tracks" VALUES (40,39,9); INSERT INTO "tracks" VALUES (41,39,10); INSERT INTO "tracks" VALUES (42,39,11); INSERT INTO "tracks" VALUES (43,38,1); INSERT INTO "tracks" VALUES (44,38,2); INSERT INTO "tracks" VALUES (45,38,3); INSERT INTO "tracks" VALUES (46,38,4); INSERT INTO "tracks" VALUES (47,38,5); INSERT INTO "tracks" VALUES (48,38,6); INSERT INTO "tracks" VALUES (49,38,7); INSERT INTO "tracks" VALUES (50,38,8); INSERT INTO "tracks" VALUES (51,38,9); INSERT INTO "tracks" VALUES (52,38,10); INSERT INTO "tracks" VALUES (53,37,1); INSERT INTO "tracks" VALUES (54,37,2); INSERT INTO "tracks" VALUES (55,37,3); INSERT INTO "tracks" VALUES (56,37,4); INSERT INTO "tracks" VALUES (57,37,5); INSERT INTO "tracks" VALUES (58,37,6); INSERT INTO "tracks" VALUES (59,37,7); INSERT INTO "tracks" VALUES (60,37,8); INSERT INTO "tracks" VALUES (61,37,9); INSERT INTO "tracks" VALUES (62,37,10); INSERT INTO "tracks" VALUES (63,37,11); INSERT INTO "tracks" VALUES (64,37,12); INSERT INTO "tracks" VALUES (65,37,13); INSERT INTO "tracks" VALUES (66,36,1); INSERT INTO "tracks" VALUES (67,36,2); INSERT INTO "tracks" VALUES (68,36,3); INSERT INTO "tracks" VALUES (69,36,4); INSERT INTO "tracks" VALUES (70,36,5); INSERT INTO "tracks" VALUES (71,36,6); INSERT INTO "tracks" VALUES (72,36,7); INSERT INTO "tracks" VALUES (73,36,8); INSERT INTO "tracks" VALUES (74,36,9); INSERT INTO "tracks" VALUES (75,36,10); INSERT INTO "tracks" VALUES (76,36,11); INSERT INTO "tracks" VALUES (77,36,12); INSERT INTO "tracks" VALUES (78,36,13); INSERT INTO "tracks" VALUES (79,36,14); INSERT INTO "tracks" VALUES (80,10,1); INSERT INTO "tracks" VALUES (81,10,2); INSERT INTO "tracks" VALUES (82,10,3); INSERT INTO "tracks" VALUES (83,10,4); INSERT INTO "tracks" VALUES (84,10,5); INSERT INTO "tracks" VALUES (85,10,6); INSERT INTO "tracks" VALUES (86,10,7); INSERT INTO "tracks" VALUES (87,10,8); INSERT INTO "tracks" VALUES (88,10,9); INSERT INTO "tracks" VALUES (89,10,10); INSERT INTO "tracks" VALUES (90,10,11); INSERT INTO "tracks" VALUES (91,10,12); INSERT INTO "tracks" VALUES (92,10,13); INSERT INTO "tracks" VALUES (93,10,14); INSERT INTO "tracks" VALUES (94,10,15); INSERT INTO "tracks" VALUES (95,10,16); INSERT INTO "tracks" VALUES (96,10,17); INSERT INTO "tracks" VALUES (97,10,18); INSERT INTO "tracks" VALUES (98,13,1); INSERT INTO "tracks" VALUES (99,13,2); INSERT INTO "tracks" VALUES (100,13,3); INSERT INTO "tracks" VALUES (101,13,4); INSERT INTO "tracks" VALUES (102,13,5); INSERT INTO "tracks" VALUES (103,13,6); INSERT INTO "tracks" VALUES (104,13,7); INSERT INTO "tracks" VALUES (105,13,8); INSERT INTO "tracks" VALUES (106,13,9); INSERT INTO "tracks" VALUES (107,13,10); INSERT INTO "tracks" VALUES (108,13,11); INSERT INTO "tracks" VALUES (109,13,12); INSERT INTO "tracks" VALUES (110,13,13); INSERT INTO "tracks" VALUES (111,13,14); INSERT INTO "tracks" VALUES (112,13,15); INSERT INTO "tracks" VALUES (113,14,1); INSERT INTO "tracks" VALUES (114,177,2); INSERT INTO "tracks" VALUES (115,178,3); INSERT INTO "tracks" VALUES (1
Re: [SQL] Re: \i command
Joel Burton wrote: > On Wed, 11 Apr 2001, Najm Hashmi wrote: > > > Hi All, > > From pgsql, I try to insert data in table using the \i command. Nothing > > takes place and after this command pgsql is hung... i cant use it any more. > > The same command works with all other tables but this is only one I am having > > problem with. > > I have attached my file with message. Could someone help me out here what is > > the reason for this behaviour. > > Works just fine for me (Pg7.1 RC3, Linux). > > Can you do manual inserts into the table? > Can you insert just a few records using \i? > Can you vacuum the table? > > -- > Joel Burton <[EMAIL PROTECTED]> > Director of Information Systems, Support Center of Washington > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] Hi, No I can't even do manaul insert on that particular table. This is really weird. Could this table's internals be corrupted? One more thing, if I run the same file on another db it works just fine. I am just wondering what is wrong with the structure. Thank you for replying. Anyone else has an idea about it Regards. begin:vcard n:Hashmi;Najm x-mozilla-html:FALSE org:Mondo-Live.com;www.flipr.com adr:;; version:2.1 email;internet:[EMAIL PROTECTED] x-mozilla-cpt:;6144 fn:Najm Hashmi end:vcard ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] To Run 2 database servers at the same time
HI all, I want to install postgresql v71.1.1 while keeping my old version of it . I think it is v7.0.3. What your advise would be for me ? I have down loaded all the file form the following link: http://www.ca.postgresql.org/ftpsite/v7.1.1/ What are the necessary step should i take to do it correctly? Any doc links would also greatly appreciateld. Thank you all for your help. Regards -- Najm Hashmi Tel:514-271-9791 www.mondo-live.com www.flipr.com
Re: [SQL] Calculating the age of a person
Hans-Jürgen Schönig wrote:
> I have a table containing the birthdays of various persons. The target
> is to compute the age of a persons.
>
> persons=# SELECT age(birth), * FROM persons LIMIT 1;
> age | id | name | birth| gender |
> income
> ---+++++
>
> 31 years 4 mons 16 days 23:00 | 1 | Albert | 1970-01-01 | m |
> 35000
> (1 row)
>
> When I use age() I don't get full years. Is there an easy way to round
> ::reltime off or up without writing a function. Is there any possibility
> to use plain SQL only?
>
> Hans
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Hey Hans try usting date_part function select
date_part('year',birth)
Regards
--
Najm Hashmi
Tel:514-271-9791
www.mondo-live.com
www.flipr.com
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] ERROR: parser: parse error at or near "$1"
HI all, I am getting this weird syntax error i.e. parse error at or near "$1" and I am not even passing any arguement to my pl/pgsql function. Here is my function , please someone could elobarte before I go nuts :) drop function populate_art_details(); create function populate_art_details() returns bool as' declare labrecord; art record; coll_ids text; lab_ids text; del text; begin del:=''|''; lab_ids:=''''; coll_ids:=''''; FOR art IN select r.artist_id,r.crh_id,r.coll_id,r.label_id, a.name,a.extrinfo from artist a, releases r where r.artist_id=a.artist_id loop FOR lab IN SELECT coll_id,label_id from releases where artist_id = art.artist_id loop coll_ids:=coll_ids||del||lab.coll_id; lab_ids:=lab_ids||del||lab.label_id; end loop; coll_ids:=coll_ids||del; lab_ids:=lab_ids||del; insert into artist_details( artist_id,crh_id,labels_id, coll_ids,name,info) values (art.artist_id,art.crh_id,lab_ids,coll_ids,art.name,art.extrinfo); end loop; return ''t''; end; ' language 'plpgsql'; select populate_art_details(); Regards, -- Najm Hashmi Tel:514-271-9791 www.mondo-live.com www.flipr.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Bit Mapping operation
Hi all, I am just wondering if bit map operations are possible in pl/pgsql. We are storing sixteen different (one or many ... i.e. check boxes ) choices in a small int. For data cruchinging reasons, we need to reverse the process to find out what what fields where selected or checked. Is this info somewhere in the docs? Thanks in advance. Regards, -- Najm Hashmi Tel:514-271-9791 www.mondo-live.com www.flipr.com
[SQL] Extracting date from epoche
Hi I have some data that is supose to be a date but in ecpoche format. How can I reonvert it to data format. Thanks in advance for help. Regards, -- Najm Hashmi Tel:514-271-9791 www.mondo-live.com www.flipr.com
[SQL] Correct syntex for implicit curor in for loops
Hi All, Could someone please tell me the Correct syntex for implicit curor in FOR loops Thank you in advance. -- Najm Hashmi Tel:514-271-9791 www.mondo-live.com www.flipr.com
