[SQL] Use of Setof Record Dynamically
Hi, I am attempting to use Setof Record dynamically. I am developing an application where I will not know what the end-user is trying to work with from a web front end. Thus I will not know before hand the structure of the table I am trying to pull the record from. My Code: CREATE FUNCTION retrievecollection(varchar, varchar, varchar) RETURNS record AS'Declaretablealias ALIAS FOR $1;crmid ALIAS FOR $2;username ALIAs FOR $3;allowed integer;objectdefinition record;realtablename char; beginselect into allowed secverf(username, tablealias);if allowed = 0 then RAISE NOTICE ''User not authorized to perform retrieve.'', allowed; RETURN false;else select into realtablename tablename from applicationaliases where tablealias = qoute_literal(tablealias); if length(crmid) = 0 then FOR objectdefinition IN SELECT * from qoute_ident(realtablename) LOOP RETURN NEXT objectdefinition; END LOOP; else FOR objectdefinition IN SELECT * from qoute_ident(realtablename) where crmid = qoute_literal(crmid) LOOP RETURN NEXT objectdefinition; END LOOP; end if;end if;RETURN record;end;'LANGUAGE 'plpgsql' VOLATILE; I know that I would execute the procedure with select * from retrievecollection as x(x typex, y typey) but how do I do this if I don't know which table was passed to the function? TIA Alex Erwin
[SQL] stored queries
Hello everyone, I'm just about to start on the reporting part of a PostgreSQL / PHP application. I think it would be a good idea to create a table in my db in which I can store queries, something like this: create table stored_queries ( id bigint primary key, name varchar, description varchar, parameters varchar[][], query text ); The frontend to it would present the user with a choice of the NAMEs of the records, check if PARAMETERs are required (the dimensions would be name and datatype), if so ask for the parameters and eventually run QUERY with these parameters. This approach seems very logical to me, so I think someone must have invented this weel already :-). I googled for "stored queries" but didn't find much. I would really like tips, feedback on this approach. Is it a bad idea, and if so, why? What pitfalls will I run into? Thanks! ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] how do i get differences between rows
Bruno Wolff III wrote: On Thu, Dec 18, 2003 at 12:21:35 +, teknokrat <[EMAIL PROTECTED]> wrote: I am after an sql query that can give the differnces between consecutive timestamp values in the resultset. anyone know how to do this? I think you can do something like the following (untested) on most systems. select a.stamp - (select max(stamp) from tablename where tablename.stamp < a.stamp) from tablename; For postgres this (also untested) might be faster if there is an index on stamp. select a.stamp - (select stamp from tablename where tablename.stamp < a.stamp order by stamp desc limit 1) from tablename; The above relies on timestamps being unique. The difference for the smallest timestamp will be null. ---(end of broadcast)--- TIP 8: explain analyze is your friend this may give me difference between current row and oldest row but what I want is row(i).timestamp - row(i-1).timestamp. Also I would like to do it for timestamp values whose hours are within normal working hours i.e 9-5. ---(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] MD5 encrypt
Hi, Is there any function on postgresql that allows to encrypt data when making an Insert statment? Tks a lot, Alvaro ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] not in vs not exists - vastly diferent performance
(B (B (BHi All, (B (BI found this interesting and thought I'd (Boffer it up for comment. (B (BI have the following delete: (B (Bdelete from tableB where id not in (select (Bid from tableA); (B (BThere are about 100,000 rows in table A, (Band about 500,000 in table B. id is indexed on both tables. This is just a (Bdevelopment DB, and I wanted to clean it up so I could put in RI constraints. (BSomewhere along the line, records have been deleted from tableA leaving orphans (Bin tableB. (B (BI launched the query yesterday afternoon, (Band it hadn't returned as of this morning, 15 hours later. Running top (Bshowed that CPU utilization was running close to 100%, and the disk was (Bnot busy at all. Anyway, I killed it and did some testing: (B (BAnalyse revealed this plan, and varying (Brandom_page_cost between 1 and 4 didn't affect it: (B (B Seq Scan on tableB (B(cost=0.00..1003619849.56 rows=251513 width=6) Filter: (NOT (B(subplan)) SubPlan -> Seq (BScan on tableA (cost=0.00..3738.64 rows=100664 width=4) (Brecoding the delete to use not exists as (Bfollows: (B (B (Bdelete from tableB where not (Bexists (select id from tableA where tableA.id = tableB.id); (B (BGave this plan: (B (BSeq Scan on tableB (B(cost=0.00..719522.41 rows=236131 width=6) Filter: (NOT (B(subplan)) SubPlan -> Index (BScan using tableB_pk on tableA(cost=0.00..3.01 rows=2 (Bwidth=4) Index (BCond: ((id)::integer = ($0)::integer) (BThis deleted 1200 rows in about 2 seconds. (BMuch better. (B (BAnyway, I was a little surprised that "not (Bin" chose to use a seq scan on the tableA in this case. I had imagined, given (Bthat statistics were up to date and the size of the table, that the plan would (Bhave been similar to that generated by not exists, or perhaps would have used a (Bhash table based on the tableA ids. (B (BSomething to think about (Banyway. (BRegards (BIain
Re: [SQL] Distributed keys
It seems to me that if the inheritance/fireign key behavior was changed so that foreign key constraints could exist within the entire inheritance tree, this problem would be solved. According to previous posts, the behavior will probably change at some point but does not appear to be a priority at the moment. If it were possible to allow FK constraints to work against the inheritance tree rather than a single table therein you could have managers, teachers, and subs as tables inherited tables from employees and the problem would be solved. Currently a workaround I can see is: Hide the actual tables in a shadow schema, and inherit as above. Have each table be represented as a view in the public schema joining the table to another table storing the employee unique identifiers. Place unique constraints on the unique identifiers table. Create rules for inserting, updating, and deleting the records. Have the Employee view search the entire inheritance tree. However, this is assuming that the data you are storing for the employees differs substantially depending on position. If this is not the case, you would do better by having a single employee table and include a field indicating whether the employee is a manager, teacher, or sub. Best Wishes, Chris Travers - Original Message - From: "Michael Glaesemann" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, December 24, 2003 4:42 AM Subject: [SQL] Distributed keys Hello all! An area of the relational database model that is not included in the SQL standard are distributed keys and distributed foreign keys. A quick example (Case 1): employees (id, name); schools (name, location); teachers (employee_id, school_name); subs (employee_id); managers (employee_id, school_name); with constraints unique employees(id) teachers(employee_id) references employees(id) teachers(school_name) references schools(name) subs(employee_id) references employees(id) managers(employee_id) references employees(id) The idea is that employees must be in one (and only one) of either teachers, subs, or managers. Currently, this might be represented in something like (Case 2) employees (id, name, employee_type, school_name); schools (name, location); employee_types (type); with constraints employees(employee_type) references employee_types(type) employees(school_name) references schools(name) where employee_types includes "teacher", "sub", and "manager" Or it might be represented with a number of rules or triggers that perform all of the necessary checking. employees(school_name) can't have a not null constraint because if the employee is a sub, they aren't associated with a school. Using the terms "distributed key" and "foreign distributed key", in the first case employee_id is a "distributed key" in that it must occur in only one of the tables teachers, subs, or managers. Distributed keys are similar in concept to primary keys—they must be unique. This guarantees an employee_id in teachers is not found in subs or managers, an employee_id in subs is not found in managers or teachers, and an employee_id in managers is not found in subs or teachers. employees(id) is a foreign distributed key in teachers, subs, and managers (as employee_id). Foreign distributed keys are similar in concept to foreign keys in that employees(id) must be referenced by a single tuple in one of teachers, subs, or managers. Another use would be in this situation (something I'm working on right now): I want to link comments by employees by employee_id, but comments from non-employees by name (as they don't have an id). comments(id, comment); comments_nonemployees(comment_id, name); comments_employees(comment_id, employee_id); with constraints comments_nonemployees(comment_id) references comments(id) comments_employees(comment_id) references comments(id) and comments(id) must be listed in either comments_nonemployees(comment_id) or comments_employees(comment_id) I haven't looked very far into how to implement distributed keys and foreign distributed keys in PostgreSQL beyond briefly looking at the pg_constraint system table, thinking a distributed key would be something making employee_id unique in teachers(employee_id) UNION subs(employee_id) UNION managers(employee_id). A distributed key is distributed over a number of tables, rather than a single one, so there'd have to be a list of relid-attnum pairs, rather than a single relid-attnum pair, such as conrelid and conkey in pg_constraint. Here's a brief sketch of the idea: pg_distributed distname name the name of the distributed key constraint distrelid oid the relid of one of the tables involved in the distributed keys distkey int2[] a list of the attnum of the columns of the table with oid distrelid involved in the distributed key distforkey bool true if foreign distributed key distfrelid oid if a foreign distributed key, the relid of the the referenced table, else 0 distfkey int2[] if a foreign distributed key, a list of
