[SQL] how to check presence of a function and set permissions?
hello! since i am now destroying and recreating my functions at every request (due to the fact that pl/psql can't cope with dynamic table names, and i didn't found anywhere a decent doc about pl/perl) i have now the problem, that a psql error is displayed if i try to drop a nonexistent function, or sometimes when i created the function through psql an ownership error, giving a permission denied to drop that function... so is there a way to check before dropping if a function exists, and is it possible to ovverride the ownership of a function? do the same GRANT mechanisms aply to functions (at least the syntax has to change a bit...)? -- ciao bboett == [EMAIL PROTECTED] http://inforezo.u-strasbg.fr/~bboett http://erm1.u-strasbg.fr/~bboett === the total amount of intelligence on earth is constant. human population is growing ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] how to check presence of a function and set permissions?
On Mon, 21 May 2001, Bruno Boettcher wrote: > since i am now destroying and recreating my functions at every request > (due to the fact that pl/psql can't cope with dynamic table names, I don't know about the rest of your question but I believe PostgreSQL supports dynamic queries in plpgsql since v7.1 -- EXECUTE 'SELECT * FROM ' || table_name || ' WHERE whatever'; However, I do not see it in the documentation, so now I'm not 100% certain. -- Tod McQuillin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] sequence problem
I have the following problem. I have a table [friends] and it looks like this: id serial name varchar(25) phone varchar(15) After I created it tehre is also a sequence that generates me the ids friens_id_seq Let's say I have 5 records. If I remove the 3th my ids will be 1,2,4,5 and my friends_id_seq=5. if I remove the 5th my ids will be 1,2,4 and my friends_id_seq=5. I want the following: if I remove a record my ids should always be like this: 1,2,3,4... and not 1,2,4,5,9,... I made a trigger that does my friends_id_seq = max(id) after delete. Can anyone help me? Thanx in advance _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] how to set ownership of tables?
Hello! how can i change the ownership of a table? i have the problem that i create table with CONSTRAINTS on other tables, those others have a different owner... and thus the creation of the table is refused is it possible to make constraints on tables whithout having their ownership? is it possible to grant those rights? or otherwise, how can i change the owenership of the concerned table? -- ciao bboett == [EMAIL PROTECTED] http://inforezo.u-strasbg.fr/~bboett http://erm1.u-strasbg.fr/~bboett === the total amount of intelligence on earth is constant. human population is growing ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] sequence problem
On Mon, 21 May 2001, gabi munteanu wrote: > I have the following problem. > > I have a table [friends] and it looks like this: >id serial >name varchar(25) >phone varchar(15) > After I created it tehre is also a sequence that generates me the ids > friens_id_seq > > Let's say I have 5 records. > > If I remove the 3th my ids will be 1,2,4,5 and my friends_id_seq=5. > if I remove the 5th my ids will be 1,2,4 and my friends_id_seq=5. > > I want the following: > if I remove a record my ids should always be like this: > > 1,2,3,4... and not 1,2,4,5,9,... > I made a trigger that does my friends_id_seq = max(id) after delete. That won't help you if you remove the 3rd in a set of 5, and there are locking issues to worry about if you have multiple transactions modifying the table since you won't see the uncommitted rows (and reading those wouldn't help anyway since they could rollback). You could probably do it if you locked the table and generated the id value (thus only allowing one transaction modification at a time others blocking until commit or rollback) and you locked and renumbered after a delete (since you don't want it in a hole state until the next is inserted). ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Define timestamp with no timezone
Hi, I have defined a table with a column of type timestamp. The value that I insert into this column is automatically appended with the timezone offset. How do I prevent this? Can I define a column as timestamp with no timezone? Thanks, -Sandhya. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] RULES
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I need something like this . CREATE RULE piezas_add AS ON insert TO piezas DO update materia_prima set usadas=(usadas+1) where n_material=new.n_material; CREATE RULE piezas_delete AS ON delete TO piezas DO update materia_prima set usadas=(usadas-1) where n_material=old.n_material; Inserting all is OK. ... but in deleting operations nothing changes. Can someone help me ?? And i would like to do the same when updating Can i do something like this ?? What is the rigth syntax ?? Must i create 2 rules to perform the action ??? CREATE RULE piezas_update AS ON update TO piezas.n_material DO update materia_prima set usadas=(usadas-1) where n_material=old.n_material ???WHAAAT??? update materia_prima set usadas=(usadas+1) where n_material=new.n_material thanks !!! fer - -- Fernando Moyano Frase del día: - -- El que retiene algo que no necesita es igual a un ladrón. (M Gandhi) (*) SymeX ==> http://symex.lantik.com (*) WDBIL ==> http://wdbil.sourceforge.net (*) Informate sobre LINUX en http://www.linux.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.4 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE7CjfWoZaf9MvtDvcRAkYbAJ9VfQGtF4NCQ8nOKegtLPKn0NwX5ACeLA7O wyQnOfKGH1xRYnYBtB6jvW8= =OB6B -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
