Re: [SQL] [PHP] PL/pgSQL and PHP 5
Glad you found the problem.
On Aug 9, 2006, at 11:42 PM, PostgreSQL Admin wrote:
$connection-execute(SELECT insert_staff_b('$staff
[insert_firstname]'::varchar));
$connection-execute(SELECT insert_staff_b('.$staff
['insert_firstname'].'::varchar));
If you are creating SQL functions you want to call from PHP, you
might be interested in this simple class:
http://pgedit.com/resource/php/pgfuncall
Then instead of all the quoting issue you have above, you could
simply call your SQL function like a normal PHP method call:
$connection-insert_staff_b($staff['insert_firstname']);
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] Function Temp Table Woes
Michael- Nice explaination of what was happening to the temp table. Thank you. -Chad --- Michael Fuhr [EMAIL PROTECTED] wrote: On Tue, Aug 08, 2006 at 10:24:02AM -0700, Chad Voelker wrote: The Goal: Return a set of records from one table based on entries in a temporary table. I'd expect this to be a commonly requested functionality. After reading the docs, support lists and googling, I haven't come up with a clean solution. My query is at the end of this post. I've come to the conclusion that returning a REFCURSOR is the best approach, but I will take other suggestions. A set-returning function would probably be easier to use; search for examples of functions declared with RETURNS SETOF tablename. Using views instead of a function might also be possible. My current issue is that the temp table (tt_occ_units) is not being dropped after commit. The second calling of this function produces the 'relation already exists' error. The temporary table is indeed being dropped; the problem is that the function creates another non-temporary table with the same name and that table isn't being dropped: CREATE TEMP TABLE tt_occ_units (unit_id INTEGER) ON COMMIT DROP; -- Get ids for all available units SELECT u.id INTO tt_occ_units The first command above creates the temporary table but then SELECT INTO creates the non-temporary table with the same name but in a different schema. Here's a simple example that shows what's happening: CREATE FUNCTION test() RETURNS void AS $$ BEGIN CREATE TEMP TABLE foo (x integer) ON COMMIT DROP; SELECT x INTO foo FROM (SELECT 1::integer AS x) AS s; END; $$ LANGUAGE plpgsql; test= \dt *.foo No matching relations found. test= BEGIN; BEGIN test= SELECT test(); test -- (1 row) test= \dt *.foo List of relations Schema | Name | Type | Owner ---+--+---+--- pg_temp_1 | foo | table | mfuhr public| foo | table | mfuhr (2 rows) test= COMMIT; COMMIT test= \dt *.foo List of relations Schema | Name | Type | Owner +--+---+--- public | foo | table | mfuhr (1 row) Notice that the temporary table went away after the commit but that the non-temporary table remained -- that's the table that's giving you trouble. Instead of using SELECT INTO you could use INSERT with a query, like this: CREATE TEMP TABLE tablename ... INSERT INTO tablename SELECT ... However, this still has a problem: after the first time you call the function subsequent calls will fail with relation with OID X does not exist. See the FAQ for the reason and how to avoid it: http://www.postgresql.org/docs/faqs.FAQ.html#item4.19 Instead of using a temporary table, consider incorporating that query directly into the main query/queries. -- Michael Fuhr __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(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] timestamp (MS SQLServer's rowversion) functionality
Tomski wrote: Hello! As many of you know, SQL Server (2000) has peculiar data type timestamp which is not SQL standard timestamp. In fact it is rowversion type. It makes tha field to be updated with current timestamp when row is updated or inserted. Is there any similiar functionality in PostgreSQL? If not, how to achieve that? I need such fields in many tables. Maybe triggers could help? Do I have to write functions for each trigger for each table? Or can it be done by one function with parameters? Partial or final solutions are welcome :) Create your table with a column of type timestamp and DEFAULT (now()) and you have the on insert functionality. You need to use triggers to get the on update fuctionality (and also for inserts if you don't trust the application to leave it default). I think this is actually covered by an example in the triggers documentation for postgres. If not then there's certainly a full code solution in the archives of this list. Please do some research before asking questions to the list. Drew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL]
I'm currently handling a migration from an Informix system to both mySQL and Postgres (same data in each). Having uploaded the DB's into both systems, from Informix, I am having some problems carrying out a query. The query, which I believe, is in standard SQL is as follows: SELECT field1, SUM(field2) as Field2, SUM(field3) FROM table1 tb1, table2 tb2 WHERE tb1.field5 BETWEEN '03/07/2006' AND '03/08/2006' AND tb1.field6 = tb2.field7 AND tb1.field8 = tb2.field8 AND tb2.field9 BETWEEN 50.00 AND 150.00 GROUP BY field1 HAVING SUM(field2) BETWEEN 95.00 AND 100.00 ORDER BY 2 DESC, field1; *The 'field(n)' and 'table(n)' values have been substituted for actual field names! The results I get from Informix and mySql return identical results, however postgres includes a great deal more i.e. 11 rows from Informix and mySQL, 203 from postgres. Does anyone have any idea why this disparity may exist? This email may contain information which is privileged or confidential. This information is intended only for the named recipient. If you are not the intended recipient, please be aware that disclosure, copying, distribution or use of this information is prohibited. If you have received this email in error, we would be grateful if you would inform us as soon as possible by telephoning +44 (0) 1769 573431, or by email to [EMAIL PROTECTED] and then delete this email. Views or opinions expressed in this email are those of the writer, and are not necessarily the views of Mole Valley Farmers Limited or its subsidiary companies. Unless specifically stated, this email does not constitute any part of an offer or contract. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] select query optimization
Hi all! i run this query select f1,f2 from t1,t2 where t1.url_id=t2.entry_id and (t1.entry_stamp::date=(now()::date-interval '14 days')) and the problem is that t1 is a very big table so the query is too slow. what if i move the second condition to where-clause. Can this reduce the select time? select f1,f2 from (select * from t1 where t1.entry_stamp::date=(now()::date-interval '14 days' ) as t1,t2 where t1.url_id=t2.entry_id and Does this makes sense? (tried to make explain analyze, but it takes too much time) Thank you. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] to_dec()
Title: to_dec() I have created a to_dec() function, which is in essence, the opposite of the to_hex() function. Does anybody know how I can submit this function to Postgresql ?
[SQL] Querying constraints?
Hi, Is is possible to query from my application whether there is a primary key or unique constraint on a given table and field name(s)? Thanks LL ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Change of data type
Use Alter table notebook from control center right click on the table and open alter table notebook you can drop a column or add a column or change the datatype Kumar On 8/7/06, Judith [EMAIL PROTECTED] wrote: Hello everybody, excuse me how can I change de data type of a field,I currently have: material character(30) but I now want the field in text type like this: materialtext somebody knows if ALTER TABLE has some option to do this?, or Howcan I do that?---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriatesubscribe-nomail command to [EMAIL PROTECTED] so that yourmessage can get through to the mailing list cleanly -- KumarDB2 DBA SAP Basis professional
[SQL] date arithmetic
Hi ALL, I want to do date arithmetic in SQL with a column that is integer. example create table bob ( col1 timestamp , col2 int4 ; where col2 represents a number of minutes. I want to do something like select col1 + interval col2 minutes from bob This is doable with most other RDBMS (DB2, Oracle MS SQL) what am I missing?? I know one option is to create col2 as interval, but does not work for me. How can I cast int4 to interval minutes -- View this message in context: http://www.nabble.com/date-arithmetic-tf2075353.html#a5715425 Sent from the PostgreSQL - sql forum at Nabble.com. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] date arithmetic
chrisj escreveu: Hi ALL, I want to do date arithmetic in SQL with a column that is integer. example create table bob ( col1 timestamp , col2 int4 ; where col2 represents a number of minutes. I want to do something like select col1 + interval col2 minutes from bob This is doable with most other RDBMS (DB2, Oracle MS SQL) what am I missing?? I know one option is to create col2 as interval, but does not work for me. How can I cast int4 to interval minutes SELECT col1 + col2*interval '1 miunute' FROM bob; []s Osvaldo ___ Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora! http://br.acesso.yahoo.com ---(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] Querying constraints?
Is is possible to query from my application whether there is a primary key or unique constraint on a given table and field name(s)? Curiously enough, I just answered a similar question on the pgsql-admin list. Try this: SELECT c.relname as table, r.conname as contraint_name, r.contype as contraint_type, pg_catalog.pg_get_constraintdef(r.oid, true) as constraint_definition FROM pg_catalog.pg_constraint r, pg_catalog.pg_class c WHERE c.oid = r.conrelid; Bye, Chris -- Chris Mair http://www.1006.org ---(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] Querying constraints?
Curiously enough, I just answered a similar question on the pgsql-admin list. Try this: SELECT c.relname as table, r.conname as contraint_name, r.contype as contraint_type, pg_catalog.pg_get_constraintdef(r.oid, true) as constraint_definition FROM pg_catalog.pg_constraint r, pg_catalog.pg_class c WHERE c.oid = r.conrelid; I forgot to mention, it's tested on 8.1... Bye, Chris. -- Chris Mair http://www.1006.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Change of data type
You can do the following:ALTER TABLE table-name ALTER colum-name TYPE text;Thanks,-- Shoaib MirEnterpriseDB (www.enterprisedb.com) On 8/8/06, Kumar Dev [EMAIL PROTECTED] wrote: Use Alter table notebook from control center right click on the table and open alter table notebook you can drop a column or add a column or change the datatype Kumar On 8/7/06, Judith [EMAIL PROTECTED] wrote: Hello everybody, excuse me how can I change de data type of a field,I currently have: material character(30) but I now want the field in text type like this: materialtext somebody knows if ALTER TABLE has some option to do this?, or Howcan I do that?---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriatesubscribe-nomail command to [EMAIL PROTECTED] so that yourmessage can get through to the mailing list cleanly -- KumarDB2 DBA SAP Basis professional
[SQL] how do I check for lower case
I have a table with a column, lets call it identifier, that is defined as varchar(8) that should never contain lower case letters. Its a large table. Is there a way to query the table to see if any values in this column are lower case and to get a list out? The user interface application that users use prevents them from adding an entry in lower case now, but didn't in earlier version. Julie begin:vcard fn:A. Juliann Meyer org:National Weather Service;Missouri Basin River Forecast Center adr;dom:;;;Pleasant Hill;MO;64080 email;internet:[EMAIL PROTECTED] title:Sr. Hydrologist - Data Systems x-mozilla-html:FALSE version:2.1 end:vcard ---(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] how do I check for lower case
On 8/10/06, Juliann Meyer [EMAIL PROTECTED] wrote: I have a table with a column, lets call it identifier, that is defined as varchar(8) that should never contain lower case letters. Its a large table. Is there a way to query the table to see if any values in this column are lower case and to get a list out? The user interface application that users use prevents them from adding an entry in lower case now, but didn't in earlier version. select * from sometable where identifier upper(identifier); Regards, Rodrigo ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] how do I check for lower case
On 8/10/06 4:32 PM, Juliann Meyer [EMAIL PROTECTED] wrote: I have a table with a column, lets call it identifier, that is defined as varchar(8) that should never contain lower case letters. Its a large table. Is there a way to query the table to see if any values in this column are lower case and to get a list out? The user interface application that users use prevents them from adding an entry in lower case now, but didn't in earlier version. select * from table where column ~ '[a-z]' Julie ---(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 -- Daryl Hell, there are no rules here-- we're trying to accomplish something. -- Thomas A. Edison ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] how do I check for lower case
Juliann Meyer wrote: I have a table with a column, lets call it identifier, that is defined as varchar(8) that should never contain lower case letters. Its a large table. Is there a way to query the table to see if any values in this column are lower case and to get a list out? The user interface application that users use prevents them from adding an entry in lower case now, but didn't in earlier version. Julie ---(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 Not sure if this is over simple and perhaps this would be a concern on a very large table but select * from table where identifier upper(identifier); would give a list of all where the entry is not all uppercase? Oisin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Breaking up a query
Hi Guys, A fellow at work has written the SQL below to retrieve some data from multiple tables. Obviously it is inefficient and unnecessarily complex, and I am trying to break it into 2 or more queries so as to enhance performance. Can you please help me do so? I would appreciate any help you can provide. I have also attached the output of the explain analyze of this query. Thank you. Saad SELECTv.xcvr_id as xcvr_id , v.bumper_number as bumper_number , v.vehicle_type as vehicle_type , p.epoch as epoch , p.latitude as latitude , p.longitude as longitude , p.fom as fom , i.version as version , i.rfid_status as rfid_status , t.tag_id as tag_id , t.tag_status as tag_status FROMpositions p LEFT OUTER JOIN data_transfers dt ON p.id = dt.position_id INNER JOIN vehicles v ON p.vehicle_id = v.id LEFT OUTER JOIN interrogations i ON p.id = i.position_id AND v.id = i.vehicle_id LEFT OUTER JOIN tags t ON i.id = t.interrogation_id WHEREp.id NOT IN ( SELECT dt.position_id FROM data_transfers WHERE dt.target_id = ? ) ORDER BY v.xcvr_id , v.bumper_number , v.vehicle_type , i.version , i.rfid_status , p.epoch; ExplainAnalyzeOutput.txt Description: application/applefile QUERY PLAN - Sort (cost=11657.83..11660.33 rows=1000 width=113) (actual time=7315.311..7323.124 rows=1487 loops=1) Sort Key: v.xcvr_id, v.bumper_number, v.vehicle_type, i.version, i.rfid_status, p.epoch - Merge Left Join (cost=11588.00..11608.00 rows=1000 width=113) (actual time=7275.853..7302.973 rows=1487 loops=1) Merge Cond: (outer.id = inner.interrogation_id) - Sort (cost=11518.17..11520.67 rows=1000 width=109) (actual time=878.566..878.858 rows=55 loops=1) Sort Key: i.id - Hash Left Join (cost=11408.83..11468.34 rows=1000 width=109) (actual time=876.369..878.208 rows=55 loops=1) Hash Cond: ((outer.id = inner.position_id) AND (outer.id = inner.vehicle_id)) - Merge Join (cost=11383.83..11433.33 rows=1000 width=101) (actual time=707.190..708.292 rows=55 loops=1) Merge Cond: (outer.id = inner.vehicle_id) - Index Scan using vehicles_pkey on vehicles v (cost=0.00..32.00 rows=1000 width=77) (actual time=0.023..0.093 rows=10 loops=1) - Sort (cost=11383.83..11386.33 rows=1000 width=28) (actual time=707.134..707.423 rows=55 loops=1) Sort Key: p.vehicle_id - Merge Left Join (cost=0.00..11334.00 rows=1000 width=28) (actual time=705.104..706.789 rows=55 loops=1) Merge Cond: (outer.id = inner.position_id) Filter: (NOT (subplan)) - Index Scan using positions_pkey on positions p (cost=0.00..32.00 rows=1000 width=28) (actual time=0.019..90.920 rows=13958 loops=1) - Index Scan using data_transfers_position_id_idx on data_transfers dt (cost=0.00..32.00 rows=1000 width=8) (actual time=0.015..91.859 rows=13903 loops=1) SubPlan - Result (cost=0.00..20.00 rows=1000 width=0) (actual time=0.017..0.017 rows=1 loops=13958) One-Time Filter: ($1 = 1) - Seq Scan on data_transfers (cost=0.00..20.00 rows=1000 width=0) (actual time=0.006..0.006 rows=1 loops=13903) - Hash (cost=20.00..20.00 rows=1000 width=24) (actual time=168.317..168.317 rows=0 loops=1) - Seq Scan on interrogations i (cost=0.00..20.00 rows=1000 width=24) (actual time=0.021..85.388 rows=13958 loops=1) - Sort (cost=69.83..72.33 rows=1000 width=12) (actual time=3334.230..4828.854 rows=284471 loops=1) Sort Key: t.interrogation_id - Seq Scan on tags t (cost=0.00..20.00 rows=1000 width=12) (actual time=0.009..1587.663 rows=284471 loops=1) Total runtime: 7355.254 ms (28 rows) ---(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]
On 4 aug 2006, at 08.55, Jonathan Sinclair wrote: SELECT field1, SUM(field2) as Field2, SUM(field3) FROM table1 tb1, table2 tb2 WHERE tb1.field5 BETWEEN '03/07/2006' AND '03/08/2006' AND tb1.field6 = tb2.field7 AND tb1.field8 = tb2.field8 AND tb2.field9 BETWEEN 50.00 AND 150.00 GROUP BY field1 HAVING SUM(field2) BETWEEN 95.00 AND 100.00 ORDER BY 2 DESC, field1; The results I get from Informix and mySql return identical results, however postgres includes a great deal more i.e. 11 rows from Informix and mySQL, 203 from postgres. Does anyone have any idea why this disparity may exist? An obvious source of ambiguity is the date comparison: tb1.field5 BETWEEN '03/07/2006' AND '03/08/2006' Is that interval a day or a month (mm/dd/ or dd/mm/)? Check your datestyle setting and make sure all systems interpret the date correctly (or at least the same). Sincerely, Niklas Johansson ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] sql error creating function
When I psql -f f.sql
I get the following error:
psql:f.sql:10: ERROR: relation fragset does not exist
CONTEXT: SQL function fragments
cat f.sql
Create Or Replace Function fragments(character varying)
Returns setof character varying
As $$
Create Temporary Table fragset (smiles character varying);
Insert into fragset Values ('COCNC');
Insert into fragset Values ('COCNCc1c1');
Select smiles from fragset;
$$ Language SQL;
But, if I paste into a running psql the commands:
Create Temporary Table fragset (smiles character varying);
Insert into fragset Values ('COCNC');
Insert into fragset Values ('COCNCc1c1');
Select smiles from fragset;
it works fine.
What is wrong in the function definition?
I'm using 8.1.3
Thanks,
TJ O'Donnell
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
