Re: [SQL] subtract two dates to get the number of days

[Thomas Kellerer]

Jean-David Beyer, 14.07.2010 19:05:

I just looked them up in my data definitions. Dates are _stored_ as type

DATE NOT NULL

Very good ;)
 

Yes, if the data happen to be stored at all. But when a program
generates the dates dynamically and wants to produce queries from them,
it is easier to use the C++ class to generate the dates.

Yes of course.


Years ago, I made a C++ data type that allowed a date datatype where I
could add, subtract, and so on.
I use it in programs that do not necessarily use a database,



To be honest: I expect the programming language to support those things.


I would love it. For all I know, the C++ Standard Library supports it
now, but I do not believe it did when I wrote that class.

Ah, those "historic" things. I primarily use Java and that had Date support 
right from the start.


Well, €0.02 is still more than my US$0.02, I believe.


Nice one :)


Regards
Thomas




--
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] Question on COUNT performance

[REISS Thomas DSIC BIP]

Hello,

You can add another JOIN in your function to avoid the test to return 
either true or false.

Here's an example:
CREATE OR REPLACE FUNCTION f_contact_acl(integer, integer)
RETURNS BOOL AS $$
   WITH contact (id_contact) AS (VALUES (1),(2),(3),(7),(8)),
customer (id_customer,id_org) AS (VALUES (4,1),(5,2),(6,3)),
customer_contact (id_contact,id_customer) AS (VALUES 
(1,4),(2,5),(3,6)),

util (id_user,id_org) AS (VALUES (1,1),(2,2))
   SELECT COUNT(*)!=0 FROM contact JOIN customer_contact ON 
contact.id_contact=customer_contact.id_contact
   JOIN customer ON 
customer.id_customer=customer_contact.id_customer

   JOIN util ON customer.id_org=util.id_org
   WHERE contact.id_contact=$2 AND util.id_user=$1;
$$ LANGUAGE SQL;   

The WITH clause and VALUES was supposed to give a test case. You simply 
have to remove them and keep the query. So the function becomes a simple 
SQL function.


Hope this helps :-)

Regards
Jean-Michel Souchard and Thomas Reiss



 Message original 
Sujet : Re: [SQL] Question on COUNT performance
De : Anders Østergaard Jensen 
Pour : pgsql-sql@postgresql.org
Date : 15/07/2010 02:14
Hi all, 

Thank you so much for your kind replies. It has all been a great help. 

I tried the SELECT COUNT(1) but that didn't yield any improvement, sorry. 

Doing the index on f_plan_event_acl( ... ) wont work, as the 
parameters are frequently shifted (the second parameter denotes the id 
of a user in another table). 

As Mr. Leeuwen rightfully points out, there might be some performance 
problems in my acl functions (these are basic functions that determine 
wether or not a user has got access to a certain row in a table or 
not---fx f_customer_acl(customer_id, user_id) will return true if the 
user has access to the customer with ID customer_id etc, the same for 
f_project_acl on projects etc).. I  am not great at optimising 
PL/pgSQL, though I have the assumption that the speed of the 
procedural language might have a great impact here. 

Before I start changing the content of the function that Mr. Leeuwen 
kindly provided above, can I pleas ask for help on how to optimise the 
other acl functions first? 


CREATE OR REPLACE FUNCTION f_contact_acl(uid integer, cid integer)
RETURNS BOOL AS $$
declare
  user record;
  contact record;
  customer record;
begin
  SELECT INTO customer cust.* FROM contacts
JOIN customer_contacts cc ON cc.contact_id = contacts.id 


JOIN customers cust ON cust.id  = cc.customer_id
WHERE contacts.id  = cid;

SELECT INTO user * FROM users WHERE id=uid;

if (customer.org_id != user.org_id) then
  return false;
end if;

return true;
end
$$ LANGUAGE 'plpgsql';
- Hide quoted text -

CREATE OR REPLACE FUNCTION f_customer_acl(uid integer, cid integer)
RETURNS BOOL AS $$
declare
  user_id integer;
  customer_id integer;
  user record;
  customer record;
begin
  user_id = $1;
  customer_id = $2;
  SELECT INTO user * FROM users WHERE id=user_id;
  SELECT INTO customer * FROM customers WHERE id=customer_id;

  -- Assert that org_id matches: 
  if (customer.org_id != user.org_id) then

return false;
  end if;

  -- Nothing more to check for:
  return true;
end;
$$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION f_doc_acl(uid integer, did integer)
RETURNS BOOL AS $$
DECLARE
  user_id   integer;
  doc_idinteger;
  user  record;
  doc   record;
  proj_rel  record;
BEGIN
  user_id := $1;
  doc_id  := $2;
  SELECT INTO user * FROM users WHERE id=user_id;
  SELECT INTO doc  * FROM documents WHERE id=doc_id;

  -- Check that org_id matches
  if (doc.org_id != user.org_id) then
return false;
  end if;

  -- If document was created by user, accept it
  if (doc.user_id_created = user_id) then
return true;
  end if;  -- if document is public, accept it
  if (doc.is_public) then
return true;
  end if;

  -- else, check the project-document relations -- is the 
  -- user member of a project that allows access to the document? 
  SELECT INTO proj_rel COUNT(*) AS acl_count FROM project_users
JOIN projects ON project_users.project_id = projects.id 

JOIN project_documents ON projects.id  = 
project_documents.project_id
JOIN documents ON project_documents.document_id = documents.id 


WHERE documents.id  = doc_id
  AND project_users.user_id = $1;

  -- acl_count returns the number of allowed relationships to exactly 
  -- this document  
  return proj_rel.acl_count > 0;

END;
$$ LANGUAGE 'plpgsql';

Would it be more beneficial to drop the functions and rewrite my basic 
queries first? However, it is a nice feature having all security 
checks wrapped into a three-four basic functions. 
If my design is completely flawed, I am also open to other design 
suggestions on how to do proper row-based access