[SQL] Joining a result set from four (4) tables

[John Tregea]

Hi,

Can anyone help me with the following?

I am setting up a series of permissions of my own making in pgSQL 8.1.4. 
I have the following tables;


resource -- a list of available resources
actions -- the actions available to the user
policies -- the actions that are allowed to be performed on individual 
resources

permissions -- matches users  with granted actions on any resource
users --  no surprises here


I have read the docs about joins but cannot get my head around the 
correct syntax. The following SQL returns all actions for every resource


SELECT
  permission.serial_id,
  resource.name,
  actions.name,
  actions.classification,
  actions.display_group,

FROM
  permission, policies, resource, actions

WHERE
  permission.user_id = '11' AND
  permission.related_id = policies.serial_id AND
  policies.status = 'Active' AND
  permission.status = 'Active'AND
  actions.status = 'Active'AND
  resource.status = 'Active'

I need a list of permissions back for each resource that a user is 
authorised to access (when they login to their GUI).


I also need to check (at user login) if every record in the chain (e.g. 
resource, action, policy and permission) is "Active" before the 
permission record is considered valid.


The list for a resource called 'Scenarios' would look something like:

11900;"Scenarios";"Publish";"Action";"B"
11900;"Scenarios";"Authorise";"Action";"B"
11900;"Scenarios";"Create";"Action";"C"
11900;"Scenarios";"Update";"Action";"C"

I am guessing it should be an inner join? but by reference book does not 
show joins on this many tables.


Thanks in advance for any help.

Regards

John T


---(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

Re: [SQL] Joining a result set from four (4) tables

[Aaron Bono]

On 7/31/06, John Tregea <[EMAIL PROTECTED]> wrote:
Hi,Can anyone help me with the following?I am setting up a series of permissions of my own making in pgSQL 8.1.4.I have the following tables;resource -- a list of available resourcesactions -- the actions available to the user
policies -- the actions that are allowed to be performed on individualresourcespermissions -- matches users  with granted actions on any resourceusers --  no surprises hereI have read the docs about joins but cannot get my head around the
correct syntax. The following SQL returns all actions for every resourceSELECT   permission.serial_id,   resource.name,   actions.name
,   actions.classification,   actions.display_group,FROM   permission, policies, resource, actionsWHERE   permission.user_id = '11' AND   permission.related_id = policies.serial_id AND
   policies.status = 'Active' AND   permission.status = 'Active'AND   actions.status = 'Active'AND   resource.status = 'Active'I need a list of permissions back for each resource that a user is
authorised to access (when they login to their GUI).I also need to check (at user login) if every record in the chain (e.g.resource, action, policy and permission) is "Active" before thepermission record is considered valid.
The list for a resource called 'Scenarios' would look something like:11900;"Scenarios";"Publish";"Action";"B"11900;"Scenarios";"Authorise";"Action";"B"
11900;"Scenarios";"Create";"Action";"C"11900;"Scenarios";"Update";"Action";"C"I am guessing it should be an inner join? but by reference book does not
show joins on this many tables.Thanks in advance for any help.Can you include the table create statements with primary and foreign keys?  That would help a lot. ==
   Aaron Bono   Aranya Software Technologies, Inc.   http://www.aranya.com==

Re: [SQL] primary keys as TEXT

[Manlio Perillo]

Aaron Bono ha scritto:
> On 7/28/06, *Manlio Perillo* <[EMAIL PROTECTED]
> > wrote:
> 
> Michael Glaesemann ha scritto:
> >
> > On Jul 28, 2006, at 17:37 , Manlio Perillo wrote:
> >
> >> There can be performancs problems in having primary keys of type
> TEXT?
> >> What about having a primary key of 3 columns (all of type TEXT)?
> >
> > What defines a problem in terms of performance is heavily dependent on
> > your particular needs and requirements. What are your
> requirements? What
> > profiling have you done to see where your performance bottlenecks
> may be?
> >
> 
> I still don't have done profiling.
> 
> Simply in the first version of my schema I used serial keys but the
> result is ugly and it force me to do a lot of joins.
> 
> 
>  
> Ugly?  Not sure what you mean by that.
> 

Because serial ids are only surrogate keys.
My tables have well definited primary keys, the only problem is that
they are of type TEXT (and spawn up to 3 columns).

My concern is: how bad can be performance?

> I do understand the problem with so many joins.  I use views so that the
> joins are only delt with once (in the database) and then all my
> applications run off the views.  That way, the applications use very
> simple queries.  The views also allow me to change the table structure
> (column names, more table normalization, etc.) without having to make
> changes to the application.
> 

View are a good idea, thanks.


Regards  Manlio Perillo

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] Triggers using PL/pgSQL

[John DeSoi]

Is it really necessary to build a SQL string and use execute? It  
seems you could just issue the INSERT statement.



On Jul 31, 2006, at 12:52 AM, Aaron Bono wrote:

CREATE OR REPLACE FUNCTION my_table_history_fn () RETURNS SETOF  
opaque AS

'
BEGIN
-- if a trigger insert or update operation occurs
IF TG_OP = ''INSERT'' OR TG_OP = ''UPDATE'' THEN
execute
''INSERT INTO my_table_history ( '' ||
''my_table_id, '' ||
''my_value, '' ||
''create_dt '' ||
'') VALUES ( '' ||
'''' || NEW.my_table_id || '', '' ||
'''' || NEW.my_value || '', '' ||
''now() '' ||
'');''
;
RETURN NEW;
END IF;
END;
'
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;




John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] DBD::Pg ... how would I format this prepare?

[Mathieu Arnold]

+-le 26/07/2006 18:29 -0300, Marc G. Fournier a dit :
| 
| I need to do:
| 
| NOW() + '2 day'::interval
| 
| where 2 is a variable ...
| 
| if I do:
| 
| NOW() + '? day'::interval
| 
| it, of course, takes the ? as a literal ... so is there some way I can do
| this such that I can do the placeholder?

I think that ! is what you're looking for.

-- 
Mathieu Arnold

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] Triggers using PL/pgSQL

[Aaron Bono]

On 7/31/06, John DeSoi <[EMAIL PROTECTED]> wrote:
Is it really necessary to build a SQL string and use execute? Itseems you could just issue the INSERT statement.I don't think so but there was some discussion a week or two ago about mixing variables and using execute.  I am curious, does anyone know what the "best" approach is?
Also, I did not address deletions.  If you still need to delete from the table, you will need to get rid of the foreign key on the history table.  You will also need to decide how the history table will reflect the recording of those deletions.
I usually don't allow deletes on tables (unless absolutely necessary) and instead add start/end dates to the tables so rows can be marked as removed.  Then I add a view that filters out the inactive rows - all applications use the views, they do not query the tables directly.  This also allows you to "delete" rows at sometime in the future or make them appear in the future too.
On Jul 31, 2006, at 12:52 AM, Aaron Bono wrote:> CREATE OR REPLACE FUNCTION my_table_history_fn () RETURNS SETOF
> opaque AS> '> BEGIN> -- if a trigger insert or update operation occurs> IF TG_OP = ''INSERT'' OR TG_OP = ''UPDATE'' THEN> execute> ''INSERT INTO my_table_history ( '' ||
> ''my_table_id, '' ||> ''my_value, '' ||> ''create_dt '' ||> '') VALUES ( '' ||> '''' || NEW.my_table_id || '', '' ||
> '''' || NEW.my_value || '', '' ||> ''now() '' ||> '');''> ;> RETURN NEW;> END IF;> END;> '
> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;John DeSoi, Ph.D.http://pgedit.com/Power Tools for PostgreSQL
-- ==   Aaron Bono   Aranya Software Technologies, Inc.   http://www.aranya.com==

Re: [SQL] DBD::Pg ... how would I format this prepare?

[Harald Fuchs]

In article <[EMAIL PROTECTED]>,
"Marc G. Fournier" <[EMAIL PROTECTED]> writes:

> I need to do:

> NOW() + '2 day'::interval

> where 2 is a variable ...

> if I do:

> NOW() + '? day'::interval

> it, of course, takes the ? as a literal ... so is there some way I can
> do this such that I can do the placeholder?

How about the following?

  now() + ? * '1 day'::interval


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Triggers using PL/pgSQL

[John DeSoi]

On Jul 31, 2006, at 10:59 AM, Aaron Bono wrote:


On 7/31/06, John DeSoi <[EMAIL PROTECTED]> wrote:
Is it really necessary to build a SQL string and use execute? It
seems you could just issue the INSERT statement.

I don't think so but there was some discussion a week or two ago  
about mixing variables and using execute.  I am curious, does  
anyone know what the "best" approach is?


I did not test with older versions, but it seems to work fine with 8.1:


CREATE OR REPLACE FUNCTION my_table_history_fn () returns trigger as
'
BEGIN
-- if a trigger insert or update operation occurs
IF TG_OP = ''INSERT'' OR TG_OP = ''UPDATE'' THEN
insert into my_table_history (
my_table_id,
my_value,
create_dt
) VALUES (
NEW.my_table_id,
NEW.my_value,
now()
);
RETURN NEW;
END IF;
END;
'
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;


insert into my_table values (1, 'test1');
insert into my_table values (2, 'test2');
update my_table set my_value = 'test3' where my_table_id = 1;
select * from my_table_history;

=== psql 9 ===
my_table_id | my_value | create_dt
-+--+
   1 | test1| 2006-07-31 11:47:33.080556
   2 | test2| 2006-07-31 11:47:48.221009
   1 | test3| 2006-07-31 11:48:21.029696
(3 rows)




John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] Triggers using PL/pgSQL

[Ken Hill]

On Sun, 2006-07-30 at 21:16 -0700, Thusitha Kodikara wrote:
> Hello,
> 
> I am interested in developing some triggers to keep track of records
> that are changed (possibly the changes of one or more specific
> columns). In addition to keeping the new values, I also need to keep
> the old values (may be on a separate table).  
> 
> Though I  have done similar things in other RDBMs using SQL, I find
> doing this in Postgres, a little bit complicated - may be because it
> needs to be done through a separate procedural language and through a
> separate function. The Postgres documentation also didn't provide much
> help ( the examples in C). I was mainly looking for example showing
> how to refer 'OLD' and 'NEW' rows using PL/pgSQL.
> 
> Can someone please direct me to some such examples?
> 
> Thanks and regards,
> -Thusitha

I too am very interested in this!

-Ken


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] Triggers using PL/pgSQL

[Milen A. Radev]

Thusitha Kodikara написа:
> Hello,
> 
> I am interested in developing some triggers to keep track of records that are 
> changed (possibly the changes of one or more specific columns). In addition 
> to keeping the new values, I also need to keep the old values (may be on a 
> separate table).  
> 
> Though I  have done similar things in other RDBMs using SQL, I find doing 
> this in Postgres, a little bit complicated - may be because it needs to be 
> done through a separate procedural language and through a separate function. 
> The Postgres documentation also didn't provide much help ( the examples in 
> C). I was mainly looking for example showing how to refer 'OLD' and 'NEW' 
> rows using PL/pgSQL.
> 
> Can someone please direct me to some such examples?


http://www.postgresql.org/docs/8.1/static/plpgsql-trigger.html


-- 
Milen A. Radev


---(end of broadcast)---
TIP 1: 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

Re: [SQL] Triggers using PL/pgSQL

[George Weaver]

- Original Message - 
From: Aaron Bono

To: John DeSoi



I don't think so but there was some discussion a week or two ago about 
mixing variables and using execute.  I am curious, does anyone >know what 
the "best" approach is?


Also, I did not address deletions.  If you still need to delete from the 
table, you will need to get rid of the foreign key on the history table. 
>You will also need to decide how the history table will reflect the 
recording of those deletions.


You may want to refer to:

http://www.varlena.com/GeneralBits/104.php (Logging Audit Changes with 
Composite Typed Columns).


It provides a very concise way of tracking all changes, possible qualifying 
as a "best approach"?


Regards,
George







---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [SQL] primary keys as TEXT

[Aaron Bono]

On 7/31/06, Manlio Perillo <[EMAIL PROTECTED]> wrote:
Because serial ids are only surrogate keys.My tables have well definited primary keys, the only problem is thatthey are of type TEXT (and spawn up to 3 columns).My concern is: how bad can be performance?
 My guess is that the performance difference is not going to be noticable unless you are dealing with huge amounts of data and even then may account for such a small hit that there will be other issues that are more pressing like writing better queries, creating a data warehouse or adding proper indexes and keeping the indexes well maintained.
Anyone care to disagree?==   Aaron Bono   Aranya Software Technologies, Inc.   http://www.aranya.com
==

Re: [SQL] Joining a result set from four (4) tables

[John Tregea]

Hi aaron,

Here are the 'create table' statements. I have indicated what are the 
primary and foreign keys with trailing comments.


Thanks

John

Aaron Bono wrote:
Can you include the table create statements with primary and foreign 
keys?  That would help a lot.


CREATE TABLE resources
(
 serial_id numeric NOT NULL, -- << Primary Key
 related_id numeric, -- << Foreign Key
 host_id int4,
 created timestamptz DEFAULT now(),
 modified timestamptz,
 valid_from timestamp,
 valid_to timestamp,
 schema_name varchar(32),
 grid_loc varchar(32),
 name varchar(32),
 status varchar(16),
 description text,
 comments text,
 sort_order int2,
 user_id int4 DEFAULT 0,
 located text,
 classification varchar(32),
 sequence_id int4,
)

CREATE TABLE actions
(
 serial_id numeric NOT NULL, -- primary key
 related_id numeric, -- foreign key on resources.serial_id
 host_id int4,
 created timestamptz DEFAULT now(),
 modified timestamptz,
 valid_from timestamp,
 valid_to timestamp,
 name varchar(32),
 status varchar(16) DEFAULT 'Active'::character varying,
 description text,
 comments text,
 sort_order int2 DEFAULT 0,
 user_id int4 DEFAULT 0, -- User_ID of the creator
 located text,
 classification varchar(32),
 sequence_id int4,
 in_box varchar(32),
 display_group varchar(2),
)

CREATE TABLE policies
(
 serial_id numeric NOT NULL, -- primary key
 related_id numeric, -- foreign key on actions.serial_id
 resource_id numeric, -- foreign key on resources.serial_id
 owner_id numeric,
 authority_id int4,
 created timestamptz DEFAULT now(),
 modified timestamptz,
 valid_from timestamp,
 valid_to timestamp,
 status varchar(16) DEFAULT 'Active'::character varying,
 description text,
 comments text,
 classification varchar(32),
 user_id int4,
 sequence_id int4,
 inheritance text,
)

CREATE TABLE permissions
(
 serial_id numeric NOT NULL, -- primary key
 related_id numeric, -- foreign key on policies.serial_id
 user_id int4, -- foreign key on users.serial_id
 owner_id int4,
 authority_id int4,
 resource_id int4,
 created timestamptz DEFAULT now(),
 modified timestamptz,
 valid_from timestamp,
 valid_to timestamp,
 name varchar(32),
 acronym varchar(6),
 status varchar(16) DEFAULT 'Active'::character varying,
 inheritance text,
 description text,
 comments text,
 sort_order int2,
 user_id int4 DEFAULT 0,
 located text,
 classification varchar(32),
 sequence_id int4,
)

CREATE TABLE users
(
 serial_id numeric NOT NULL, -- primary key
 created timestamptz DEFAULT now(),
 modified timestamptz,
 valid_from timestamp,
 valid_to timestamp,
 name varchar(64) NOT NULL,
 acronym varchar(6),
 status varchar(16),
 inheritance text,
 description text NOT NULL,
 comments text NOT NULL,
 sort_order int2 NOT NULL,
 clearance varchar(32) NOT NULL,
 administrator bool DEFAULT false,
 user_id int4 DEFAULT 0,
 next_serial_id int4 DEFAULT 1,
 classification varchar(32),
)

---(end of broadcast)---
TIP 6: explain analyze is your friend