[SQL] Use of INNER JOIN and others ??!?
Is it possible to use the statements INNER JOIN, OUTER JOIN, LEFT JOIN and RIGHT JOIN in the new version of PostgreSQL 7.1 ? Luis Sousa
[SQL] Reading array's of integer in plpg/SQL
>From pg_group, it's possible to return the users that are in that group. How can in put the values in that array of integer to an array ?? Using plpg/SQL, how can i access to the array of integers ??? Best Regards Luis Sousa ---(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] Hidden Select
I have a query that joins several table with some restrictions. I want to take out those restrictions from the query and create a view that does not show the fields that are used to restrict the data, but in some way, i can restrict that data when i call the view. Is it possible to hide some fields in the view so it could be possible to restrict that data Best Regards Luis Sousa ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Rule ON DELETE, to perform to DELETE querys !
I have a view over a join of tables and when it's performed over the view a delete i want to delete records in two different tables. The code that i wrote was: CREATE RULE "deletetables" AS ON DELETE TO "tables" DO INSTEAD ( DELETE FROM table2 WHERE id = OLD.id; DELETE FROM table1 WHERE id=OLD.id ); table2 references table1 by the field id only for update. I don't them to be referenced by delete ! When i execute: DELETE FROM tables WHERE id=1; i got these message from postgres: 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. The records exists in both tables !!! This already happen with some of you How can i do this Thanks Luis Sousa ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Executing RECORD's inside a FUNCTION
Hello I have a function with a field record named 'starting'. Now I want to use starting in a query. Is it possible ? I use it with a simple join inside the query a I got the message that table starting is not defined !!! Best Regards Luis Sousa ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Permission on insert rules
Hello everybody, Just a question. I'm writing some rules to insert/update some data in my database, and I gave all the privileges on that view to the user, and only select on the tables. When that user inserts data using the view, I thought that was user postgres that will do the rest ! But I got permission denied on those tables. The idea was to create a layer, with the views, giving to that user permission on views to insert and update, and not to tables. Is this possible ? Thanks in advance. Luis Sousa smime.p7s Description: S/MIME Cryptographic Signature
Re: [SQL] Permission on insert rules
Bruce Momjian wrote: Josh Berkus wrote: Luis, Just a question. I'm writing some rules to insert/update some data in my database, and I gave all the privileges on that view to the user, and only select on the tables. When that user inserts data using the view, I thought that was user postgres that will do the rest ! But I got permission denied on those tables. The idea was to create a layer, with the views, giving to that user permission on views to insert and update, and not to tables. Is this possible ? This is a known problem. I know that permissions for Functions has been addressed in 7.3. However, I am not sure about permissions for updatable views. Tom, Bruce? Views have always had their own permissions. Offcourse, but when I'm giving permissions to insert and update on views, I have to give those permissions also to the tables !! (those operations that are executed on rules) Luis Sousa smime.p7s Description: S/MIME Cryptographic Signature
Re: [SQL] Permission on insert rules
Robert Treat wrote: On Fri, 2002-11-08 at 21:40, Bruce Momjian wrote: Josh Berkus wrote: Luis, Just a question. I'm writing some rules to insert/update some data in my database, and I gave all the privileges on that view to the user, and only select on the tables. When that user inserts data using the view, I thought that was user postgres that will do the rest ! But I got permission denied on those tables. The idea was to create a layer, with the views, giving to that user permission on views to insert and update, and not to tables. Is this possible ? This is a known problem. I know that permissions for Functions has been addressed in 7.3. However, I am not sure about permissions for updatable views. Tom, Bruce? Views have always had their own permissions. If the functions can fire as there creator instead of there caller, then I would think as long as the creator has insert/update views on the base table, you should be able to do updateable rules and give only permissions to the view for the caller. (Though maybe you have to use triggers rather than rules to do this?) Does that sound right? Robert Treat Is that the only way to do it ? Luis Sousa smime.p7s Description: S/MIME Cryptographic Signature
Re: [SQL] Permission on insert rules
Josh Berkus wrote: Luis, Just a question. I'm writing some rules to insert/update some data in my database, and I gave all the privileges on that view to the user, and only select on the tables. When that user inserts data using the view, I thought that was user postgres that will do the rest ! But I got permission denied on those tables. The idea was to create a layer, with the views, giving to that user permission on views to insert and update, and not to tables. Is this possible ? I just checked this. It works fine in 7.2.3. I think that you are missing a step. If you want to have an updatable view, then you need to define a Rule for updating it, such as: kitchen=# create rule update_password as on update to user_password kitchen-# do instead update "user" set "password" = NEW."password" kitchen-# where user_id = OLD.user_id; See the online docs, under Server Programming, for how to use the RULES system. That's what I already made. The problem is when I do the update, I permission denied in all the tables for update and insert. The user that's making this operation only have select privilege. Any way, I'm using version 7.2.1-2 for debian. Luis Sousa smime.p7s Description: S/MIME Cryptographic Signature
Re: [SQL] Permission on insert rules
Hi again,
I already know what's the problem. Actually, everything works fine in
the example posted by Robert. Part of my rule is as simple as that
example, but I'm also calling functions inside the rule.
I have a table, whose primary key is a serial, that is connected to a
few tables. In this view, I want to insert data, in the main table, and
also in the "child" tables. My idea was to create a rule, that first
inserts in the parent table, and some functions, that will select the
parent table returning the id created, and will insert some data on
child table (I'm open for sugestions to do this !!!). I don't know
exactly how this works if more than one user at the same time
When inserting, using the rule, the insert that's defined on the rule
works fine, but the insert defined inside the function, doesn't (that's
the one that gives permssion denied).
Suppose these definitions:
-- Tables definition
CREATE TABLE "pessoal" (
"idPessoal" serial,
"titulo" text default '',
"nome" text NOT NULL,
PRIMARY KEY ("idPessoal")
);
CREATE TABLE "pessoalGabinete" (
"idPessoal" int4,
edificio text,
sala text,
PRIMARY KEY ("idPessoal",edificio,sala),
FOREIGN KEY("idPessoal") REFERENCES pessoal
ON UPDATE CASCADE
);
-- View definition
CREATE VIEW "pessoalInfo_v" AS
SELECT p.titulo, p.nome, pg.edificio, pg.sala
FROM pessoal p LEFT OUTER JOIN "pessoalGabinete" pg USING ("idPessoal");
-- Function definition
CREATE FUNCTION "pessoalInfoGab_f_insert"(text,text)
RETURNS boolean AS '
DECLARE
f_edificio ALIAS FOR $1;
f_sala ALIAS FOR $2;
pessoal RECORD;
BEGIN
SELECT MAX("idPessoal") AS max INTO pessoal
FROM pessoal;
INSERT INTO "pessoalGabinete" ("idPessoal",edificio,sala)
VALUES (pessoal.max,f_edificio,f_sala);
RETURN 1;
END; '
LANGUAGE 'plpgsql';
-- Rule definition
CREATE RULE "pessoalInfo_r_insert" AS ON INSERT TO "pessoalInfo_v"
DO INSTEAD (
INSERT INTO pessoal (titulo,nome)
VALUES (NEW.titulo,NEW.nome);
SELECT "pessoalInfoGab_f_insert"(NEW.edificio,NEW.sala) AS ok;
);
GRANT SELECT,INSERT,UPDATE on "pessoalInfo_v" to nobody;
GRANT INSERT,UPDATE on "pessoal_idPessoal_seq" to nobody;
INSERT INTO "pessoalInfo_v" (titulo,nome,edificio,sala) VALUES
('Dr.','Robert','A',5);
And I got this message:
NOTICE: Error occurred while executing PL/pgSQL function
pessoalInfoGab_f_insert
NOTICE: line 10 at SQL statement
ERROR: pessoalGabinete: Permission denied.
But, suppose that I use this rule instead and that already exists in
table pessoal "idPessoal"=1:
-- Rule definition
CREATE RULE "pessoalInfo_r_insert" AS ON INSERT TO "pessoalInfo_v"
DO INSTEAD (
INSERT INTO pessoal (titulo,nome)
VALUES (NEW.titulo,NEW.nome);
INSERT INTO "pessoalGabinete" ("idPessoal",edificio,sala)
VALUES (1,NEW.edificio,NEW.sala);
);
In this case everything works fine, but this doesn't solve my problem,
because I need to know whats the number created by the sequence in pessoal.
Any ideas ??
Thanks in advance.
Luis Sousa
Robert Treat wrote:
This should be a test case for what Luis wants, although it works in
7.2.1 so maybe not. Luis, if this isn't what your trying to do, you'll
need to post some code:
create table parent (id int, name text, misc text);
create view child as select id,name from parent;
create rule jammasterjay as on insert to child do instead insert into
parent values (new.id,new.name);
insert into parent values (1,'one','wahad');
insert into parent values (2,'two','ithnain');
insert into parent values (3,'three','thalata');
select * from parent;
select * from child;
insert into child (4,'four');
select * from parent;
create user mellymel;
grant select on child to mellymel;
grant insert on child to mellymel;
** reconnect as mellymel **
select * from parent; (generates error)
select * from child;
insert into child values (5,'five');
select * from child; (has all 5 rows)
Robert Treat
On Tue, 2002-11-12 at 12:29, Josh Berkus wrote:
Luis,
That's what I already made. The problem is when I do the update, I
permission denied in all the tables for update and insert. The user
that's making this operation only have select privilege.
Any way, I'm using version 7.2.1-2 for debian.
I can't reproduce the problem, and permissions did not get fixed
between 7.2.1 and 7.2.3. So I'm pretty sure that you're miss
Re: [SQL] Permission on insert rules
Tom Lane wrote: Luis Sousa <[EMAIL PROTECTED]> writes: When inserting, using the rule, the insert that's defined on the rule works fine, but the insert defined inside the function, doesn't (that's the one that gives permssion denied). Right. As of 7.3 you can fix this by making the function "setuid" (ie, it runs with the permissions of the function owner, not the caller). There's any way to insert data inside the tables, using the functions, called by the rules, without giving direct access to the user ? I don't know, using a trigger or any kind of structure !!?? Regards, Luis Sousa smime.p7s Description: S/MIME Cryptographic Signature
Re: [SQL] Problems invoking psql. Help please.
Hi there, I'm using debian woody. If you post your configuration files that are in /etc/postgresql/ maybe i can help you. Regards, Luis Sousa Hugh Esco wrote: Hey folks: I am able to consistently start and stop the postgreSQL server and to access it across our office network with pgAdmin II. I have had no luck invoking the psql command line prompt, from where I can enter queries and start to surmount the learning curve from background with mySQL to my next step with postgreSQL. Can anyone help me figure out, please, what this is about and what I can do about it? I've copied the shell dialogue below. Everything in: /usr/lib/postgresql/bin is owned by root:root. Who should these files be owned by? The database engine is invoked as postgres, which is the user which created the databse. Do I harm anything if I chown postgres:postgres for everything in that directory? Will this get me past this error and to a psql prompt? I am operating on a Debian Woody Platform, with postgreSQL 7.2.1 and ODBC driver 7.1.9. All help is appreciated. Thanks. -- Hugh Esco hesco@biko:~$ su postgres Password: postgres@biko:/home/hesco$ locate psql /usr/bin/psql /usr/lib/odbc/libodbcpsqlS.so /usr/share/man/man1/psql.1.gz /var/home/hesco/.psql_history postgres@biko:/home/hesco$ ./psql sh: ./psql: No such file or directory postgres@biko:/home/hesco$ psql env: /usr/lib/postgresql/bin/readpgenv: Permission denied No database specified postgres@biko:/home/hesco$ psql ggp_test env: /usr/lib/postgresql/bin/readpgenv: Permission denied Could not execv /usr/lib/postgresql/bin/psql postgres@biko:/home/hesco$ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster smime.p7s Description: S/MIME Cryptographic Signature
Re: [SQL] Problems invoking psql. Help please.
Tom Lane wrote: Hugh Esco <[EMAIL PROTECTED]> writes: Any ideas on what my next steps should be would be greatly appreciated. Start over: delete your PG installation and reinstall the Debian package. It seems very clear that you've got an incomplete package. regards, tom lane I agree with Tom Lane. Probably is the best thing to do. When you install all it over, in theory, all the problems will solve by them selfs. Luis Sousa smime.p7s Description: S/MIME Cryptographic Signature
Re: [SQL] Problems invoking psql. Help please.
Did you install your package using apt-get ? All the instalations that I do are using those tools from debian. You have to see all the packages that you have instaled in your computer like: dpkg -l | grep postgresql ii postgresql 7.2.1-2Object-relational SQL database, descended fr ii postgresql-cli 7.2.1-2Front-end programs for PostgreSQL ii postgresql-con 7.2.1-2Additional facilities for PostgreSQL Then you do: dpkg --purge postgresql. You can now run the first command to see if something is still installed. If some are instaled, the you remove it using again dpkg --purge. Luis Sousa Hugh Esco wrote: I have reinstalled before. I wonder though, how I ensure that I have cleanly un-installed it first, so that I leave no residue from the previously botched installation around to mess things up the next time. -- Hugh Esco At 09:03 AM 11/19/02 +, Luis Sousa wrote: Tom Lane wrote: Start over: delete your PG installation and reinstall the Debian package. It seems very clear that you've got an incomplete package. regards, tom lane I agree with Tom Lane. Probably is the best thing to do. When you install all it over, in theory, all the problems will solve by them selfs. Luis Sousa ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) smime.p7s Description: S/MIME Cryptographic Signature
Re: [SQL] Problems invoking psql. Help please.
biko:/usr/bin# psql -U postgres No database specified Instead, do: su - postgres Then do: psql template1 or psql -h template1 My pg_hba.conf temporarily reads: local all trust host all 127.0.0.1 255.255.255.255 trust host template1 192.168.2.21 255.255.255.0 trust For now, just remove any security and put this line: host all 192.168.2.21 255.255.255.255 trust smime.p7s Description: S/MIME Cryptographic Signature
Re: [SQL] trying to learn plpqsql... so please forgive..
When I do an 'INSERT INTO VALUES ' and on the table is a serial primary key named p_key. As I want this number to be auto-generated, but use it as a 'customer number', I want to create this function to return the value of this insert. Try this: SELECT currval(); Luis Sousa smime.p7s Description: S/MIME Cryptographic Signature
Re: [SQL] SQL query help!
Tell me what did you try with limit and group by. Where's IN, why don't you use EXISTS instead. It runs much master ! Regards, Luis Sousa Arcadius A. wrote: Hello! I hope that someone here could help. I'm using PostgreSQL7.1.3 I have 3 tables in my DB: the tables are defined in the following way: CREATE TABLE category( id SERIAL NOT NULL PRIMARY KEY, // etc etc ) ; CREATE TABLE subcategory( id SERIAL NOT NULL PRIMARY KEY, categoryid int CONSTRAINT subcategory__ref_category REFERENCES category (id) // etc etc ) ; CREATE TABLE entry( entryid SERIAL NOT NULL PRIMARY KEY, isapproved CHAR(1) NOT NULL DEFAULT 'n', subcategoryid int CONSTRAINT entry__ref_subcategory REFERENCES subcategory (id) // atd , ) ; I have the following SQL query : "SELECT * FROM entry where isapproved='y' AND subcategoryid IN (SELECT id FROM subcategory WHERE categoryid='"+catID+"') ORDER BY subcategoryid DESC"; For a given categoryid( catID), the query will return all entries in the "entry" table having a corresponding subcategoryid(s)[returned by the inned subquery]. But I want to return only a limited number of entries of each subcategory. let's say that I want to return at most 5 entries of each subcategory type ( for instance if the inner subquery returns 3 results, thus I will be having in total at most 15 entries as relust) How can this be achieved? I'm aware of postgreSQL "LIMIT" and "GROUP BY" clause. but so far, I'm not able to put all this together... Thanks in advance. Arcadius. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster smime.p7s Description: S/MIME Cryptographic Signature
Re: [SQL] plpgsql recursion
Can you post the recursion function that you use on this case to get out the information from the table ? Thanks, Luis Sousa Stefano Vita Finzi wrote: Greetings! I have a table like: node parent 1 2 2 3 3 4 Since i traverse this table with a recursive function, i want to avoid infinite recursion loop. I have wrote a function to check that a new record does not create a circular dependency. The function i wrote is as follow: CREATE OR REPLACE FUNCTION dba_test(INTEGER,INTEGER) RETURNS TEXT AS ' DECLARE traversing ALIAS FOR $1; testing ALIAS FOR $2; t_rec RECORD; BEGIN FOR t_rec IN SELECT node,parent FROM dba_test WHERE parent = traversing LOOP IF t_rec.node = testing THEN RETURN ''Circular''; ELSE PERFORM dba_test(t_rec.node,testing); END IF; END LOOP; RETURN ''ok'' || testing::text; END; ' LANGUAGE 'plpgsql'; I would use this function BEFORE inserting the new row. But if i try SELECT dba_test(4,1); i don't have the result i expect. Can i you give me an hint where am i wrong? Thank you! Stefano Vita Finzi [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Setuid functions
Hi all, How can I set a function as setuid ? I take a look at the documetation, on Reference Manual and the only reference I saw to it was on SET SESSION AUTHORIZATION. Thanks in advance. Luis Sousa ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Database diagram
Ganesan Kanavathy wrote: I have a postgres database with many tables. How do I create database diagram? Are there any free tools available to create database diagram from pgsql database? Regards, Ganesan ---(end of broadcast)--- TIP 8: explain analyze is your friend Did you already tried dia? I never used it, but someone told me that imports data definition from PostgreSQL. You can try it. Luis Sousa smime.p7s Description: S/MIME Cryptographic Signature
[SQL] Problem in age on a dates interval
Hi all,
I'm using PostgreSQL 7.3.3 on i386-pc-linux-gnu, compiled by GCC gcc
(GCC) 3.3 (Debian), and I don't understand the results of the following
queries:
SELECT age('2004-05-14 16:00'::timestamp,'2004-02-18 16:00'::timestamp);
age
2 mons 25 days
SELECT '2004-02-18 16:00'::timestamp+'2 mons 25 days'::interval;
?column?
-
2004-05-13 16:00:00
In this case, the age from 2004-05-14 16:00 to 2004-02-18 16:00 is 2
mons 25 days, but if I add the age to the initial date, it returns one
day less!?
SELECT age('2004-05-26 16:00'::timestamp,'2004-02-18 16:00'::timestamp);
age
---
3 mons 8 days
SELECT '2004-02-18 16:00'::timestamp+'3 mons 8 days'::interval;
?column?
-
2004-05-26 16:00:00
Here, the age between 2004-05-26 16:00 and 2004-02-18 16:00 is 3 mons 8
days, and this interval added to the initial date gives the correct result!!
Best regards,
Luis Sousa
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Problem in age on a dates interval
I worked around this problem returning the difference between the two dates, using extract doy from both. Anyway, this will cause a bug on my code when changing the year. Any ideas? Best regards, Luis Sousa Tom Lane wrote: Theodore Petrosky <[EMAIL PROTECTED]> writes: wow at first I thought I had my head around a leap year problem so I advanced your query a year I think what's going on here is a difference of interpretation about whether an "M months D days" interval means to add the months first or the days first. For instance 2005-02-18 plus 2 months = 2005-04-18, plus 24 days = 2005-05-12 2005-02-18 plus 24 days = 2005-03-14, plus 2 months = 2005-05-14 The timestamp-plus-interval operator is evidently doing addition the first way, but it looks like age() is calculating the difference in a way that implicitly corresponds to the second way. I have some vague recollection that this has come up before, but I don't recall whether we concluded that age() needs to be changed or not. In any case it's not risen to the top of anyone's to-do list, because I see that age() still acts this way in CVS tip. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Problem in age on a dates interval
Yes, that's a much more clever solution than the one I used. Thanks Best regards, Luis Sousa Alexander M. Pravking wrote: On Mon, Jul 19, 2004 at 10:00:50AM +0100, Luis Sousa wrote: I worked around this problem returning the difference between the two dates, using extract doy from both. Anyway, this will cause a bug on my code when changing the year. Any ideas? Why don't you use the minus operator? SELECT '2004-05-14 16:00'::timestamp - '2004-02-18 16:00'::timestamp; ?column? -- 86 days Or, if you need the age just in days: SELECT extract(day from '2004-05-14 16:00'::timestamp - '2004-02-18 16:00'::timestamp); date_part --- 86 or SELECT '2004-05-14 16:00'::date - '2004-02-18 16:00'::date; ?column? -- 86 Note that '2004-05-14 16:00'::date is actually '2004-05-14 00:00'::date, so the last two are not always equal. Tom Lane wrote: Theodore Petrosky <[EMAIL PROTECTED]> writes: wow at first I thought I had my head around a leap year problem so I advanced your query a year I think what's going on here is a difference of interpretation about whether an "M months D days" interval means to add the months first or the days first. For instance 2005-02-18 plus 2 months = 2005-04-18, plus 24 days = 2005-05-12 2005-02-18 plus 24 days = 2005-03-14, plus 2 months = 2005-05-14 The timestamp-plus-interval operator is evidently doing addition the first way, but it looks like age() is calculating the difference in a way that implicitly corresponds to the second way. I have some vague recollection that this has come up before, but I don't recall whether we concluded that age() needs to be changed or not. In any case it's not risen to the top of anyone's to-do list, because I see that age() still acts this way in CVS tip. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] CREATE TEMPORARY TABLE ON COMMIT DROP
Hi all, I'm tryng to write query that on commit drops the temporary table but I'm doing someting wrong. According do documentation, I can create a temporary table using this syntax: CREATE TEMP TABLE table_name AS SELECT * from table_select; or I can create a table like this: CREATE TEMP TABLE table_name (fld1 integer) ON COMMIT DROP; But how can I create a table using a query and putting ON COMMIT DROP. Putting this on the end of the first sentence (CREATE TEMP TABLE table_name AS SELECT * from table_select ON COMMIT DROP;) gives the ERROR: syntax error at or near "ON" at character 60. Thanks in advance for you answers. Luis Sousa ---(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
