[SQL] kind of RULE on SELECT depending on existence of a WHERE condition
Hi please help me with the following scenario: I'd gladly have all persons (contacts, customers, staff etc) in one table "people" with some distinctive flags. My reason for this, is that simple contacts could become customers or even staff, staff members could be in the same time also customers etc and I would be glad to manage all the data in one table to avoid the same Person-Record in three tables. Now, the SELECTs would be generated from an application (hibernate) automatically inserting some WHERE condition on the flags of the recorded people depending on the rights of the user. Some isn't allowed to see staff, an other shouldn't see customers etc. But - for the case, that an application-programmer or even I (tired, middle in the night) forget to program the insertion of the needed limiting WHERE condition - it would be great to have some sort of rule ON SELECT, so a SELECT without WHERE on the flags would return NOTHING. Is there a way to achieve this? Thank you for reading and hoping my problem can be solved. Rawi -- View this message in context: http://old.nabble.com/kind-of-RULE-on-SELECT-depending-on-existence-of-a-WHERE-condition-tp27741669p27741669.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] kind of RULE on SELECT depending on existence of a WHERE condition
On 01/03/10 10:07, rawi wrote: But - for the case, that an application-programmer or even I (tired, middle in the night) forget to program the insertion of the needed limiting WHERE condition - it would be great to have some sort of rule ON SELECT, so a SELECT without WHERE on the flags would return NOTHING. Is there a way to achieve this? Not quite the way you suggest. You could build a series of views with the WHERE conditions built in to them, and grant permissions on those though. -- Richard Huxton Archonet Ltd -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] kind of RULE on SELECT depending on existence of a WHERE condition
> Not quite the way you suggest. You could build a series of views with > the WHERE conditions built in to them, and grant permissions on those > though. Thank you very much for your help. Unfortunately is this not what I hoped... The permissions will be granted dynamic by the application out of the user-records and expressed in the WHERE flags. I'll need another approach... Cheers, Rawi -- View this message in context: http://old.nabble.com/kind-of-RULE-on-SELECT-depending-on-existence-of-a-WHERE-condition-tp27741669p27742718.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] placement of stored procedures and contrib modules
We were trying to use a separate schema to put contrib modules, stored procedures and triggers from our database schema. We called the one where we stored all of the modules isscontrib and the other iss which is where the db is stored. We've fully qualified the names of our items from isscontrib and have also included that schema in the search path. Our goal is to eventually mark isscontrib as execute only. Is there any restrictions to having objects in a separate schema? We are using the citext contrib module. It doesn't appear to work when it's in the isscontrib module. We don't get any error messages however we also don't get the results we are looking for, namely case insensitive search results. However, if we move the contrib module to the public schema, everything appears to work fine. Best Regards Mike Gould -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] kind of RULE on SELECT depending on existence of a WHERE condition
On 01/03/10 12:16, rawi wrote: Not quite the way you suggest. You could build a series of views with the WHERE conditions built in to them, and grant permissions on those though. Thank you very much for your help. Unfortunately is this not what I hoped... The permissions will be granted dynamic by the application out of the user-records and expressed in the WHERE flags. I'll need another approach... You could write a set-returning function that takes either: 1. A list of conditions 2. The text for a WHERE clause If it gets no conditions or a blank string, it returns nothing. You will need to create the function with SECURITY DEFINER permissions, as a user who can read from the table. Make sure the application cannot read from the table and has to use the function. -- Richard Huxton Archonet Ltd -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Using new.id in the WHERE condition of one UPDATE inside ON INSERT rule
On Jueves 25 Febrero 2010 16:28:56 usted escribió: > Aron writes: > > I would like to avoid using "SELECT MAX(id) FROM my_table" (it doesn't > > seem a good method), but if I use "new.id", I get new id values, not the > > "id" inserted with the rule, and the condition is always false. > > "new.id" is a macro, which in this example will be expanded into a > nextval() function call, which is why it doesn't work --- the nextval() > in the WHERE condition will produce a different value from the one in > the original INSERT. You would be far better off using a trigger here > instead of a rule. > > regards, tom lane > Thank you very much. I've used this trigger succesfully: CREATE OR REPLACE FUNCTION copy_cost RETURNS "trigger" AS ' DECLARE change_other BOOL; BEGIN IF (tg_op = ''UPDATE'') THEN IF (new.id_other <> old.id_other) THEN change_other = true; ELSE change_other = false; END IF; END IF; IF (tg_op = ''INSERT'' AND new.my_cost IS NULL) OR change_other) THEN new.my_cost = ( SELECT my_other_cost FROM my_other_table WHERE id = new.id_other ); END IF; RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER copy_cost__trigger BEFORE INSERT OR UPDATE ON my_table FOR EACH ROW EXECUTE PROCEDURE copy_cost(); -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Create functions using a function
Hi all, is there a way to define functions and/or triggers in a function? For example, can I create a function that takes an argument and defines a function that has the name passed as argument to the first function? Something like this . CREATE FUNCTION test (name varchar) RETURNS VOID AS $$ BEGIN CREATE FUNCTION name ( ) (here "name" should be expanded to the variable content) RETURNS ... END $$ Is there a way to do this? Thanks
Re: [SQL] Create functions using a function
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Yes, it's possible. Use EXECUTE. There is one example under "Example 38-9. Porting a Procedure from PL/SQL to PL/pgSQL" http://www.postgresql.org/docs/8.4/static/plpgsql-porting.html Also have look in: http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html under "37.6.5. Executing Dynamic Commands" Petru Ghita Gianvito Pio wrote: > Hi all, is there a way to define functions and/or triggers in a > function? > > For example, can I create a function that takes an argument and > defines a function that has the name passed as argument to the > first function? > > Something like this . > > CREATE FUNCTION test (name varchar) RETURNS VOID AS $$ BEGIN CREATE > FUNCTION name ( ) (here "name" should be expanded to the variable > content) RETURNS ... > > END $$ > > Is there a way to do this? Thanks > -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkuMQdYACgkQt6IL6XzynQSjoQCffcPZ2W69uy3wpGlkhkraQm2a p0IAnA+8njdC6nAdpdhaTg/M9QEohYVb =/tvu -END PGP SIGNATURE- -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
