[SQL] query PostgreSQL from c++
Hi folks, I'm asked to write a "small" c++ application to query a PostgreSQL DB. In principel I can't see why this should be so difficult but I don't really know a lot about the API. Therefor I thought I just ask if anyone has done this before - of course I think - and might be able and willing to give me a few tips. Maybe there's even a document somewhere which I could consult? So if someone has tipps and/or examples or maybe a suggestion for a book, please mail me, chris ---(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] RULES
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Well ... i'm testing carefully the two rules ==> CREATE RULE piezas_add AS ON insert TO piezas DO update materia_prima set usadas=(usadas+1) where n_material=new.n_material; CREATE RULE piezas_delete AS ON delete TO piezas DO update materia_prima set usadas=(usadas-1) where n_material=old.n_material; Inserting is OK. Deleting is broken if it deletes more than one row. The rule is executed only one time each delete command, and not one time each deleted row. It's this OK ??? Am i wrong ??? It's a "feature" ?? ;-) Thanks - -- Fernando Moyano Frase del día: - -- ¡CocaCola esasín! ¡prts, brps! (*) SymeX ==> http://symex.lantik.com (*) WDBIL ==> http://wdbil.sourceforge.net (*) Informate sobre LINUX en http://www.linux.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.4 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE7Cp+HoZaf9MvtDvcRAqV/AKCQLhl34VF3sfPt3O5i5w5MFpcZRQCgmm5b duRciSRJhzTJLuhFrNLUcWQ= =It2U -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Re: timestamp bug
Cedar Cox wrote: > > There appears to be a bug in timestamp/interval addition. It happens in > both PG version 7.0.2 and 7.1. There is a duplicate day (2001 Sep 07) and > a missing day (2002 Apr 04). I discovered this by accident when I asked > the interface I'm writing for a 365 day long calendar.. Interestingly, > the missing day thing (second example) doesn't happen if only adding a few > days (like the first example). I didn't go into detail to find the point > at which it does happen. IMN1=# SELECT version(); version --- PostgreSQL 7.1.1 on i686-pc-linux-gnu, compiled by GCC 2.95.3 (1 row) And all right work. Try new version. ---(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] Set Datestyle
Hallo, i have postgres 7.1 in Radhat 6.5 the Datestype default is ISO format where is change default Datestyle to Postgres format ?? i used Set Datestyle to postgres in plsql test#>select now() now 2001-05-21 09:55:36+07 test#>set datestyle to postgres; SET VARIABLE test#>select now(); now --- Mon May 21 09:56:19 2001 JAVT but when i reconnect to plsql the datestype ISO again Help me plz Nizomi ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] convert date to time?
Hi! I am migrating a bunch of cf applications that are using access as a backend, and pgadmin is converting all date/time data types to date data types on the postgres side. I want to convert these columns to the appropriate time data types, but am unable to using CAST( according to the pop-up window.) Is there another way? Thanks! -- Shannon Peevey UNT-Central Web Support [EMAIL PROTECTED] 940-369-7786 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] c++ wrapper library
hi, i wonder if there exists a c++ wrapper library to libpq (or native c++ library to postgresql). I'd like to be able to develop c++ applications importing classes from libraries: classes like database connections, data sources, sql-queries, much like for example de database-components in Delphi accomplish. i want to avoid to have to write much interface code and to rewrite again and again error control blocks. does this exist? tx ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Re: Transposing data
I have also thought of a solution like that but the problem is that I do not have a complete list of all values occuring in the column so it has to be dynamical. The second problem is that the amount of data involved may become huge. Hans Alexander Dederer schrieb: > Hans-J?rgen Sch?nig wrote: > > > I want the values in column label to be displayed in the a-axis. Is > > there an easy way to transform the data: > > Here is the input data: > > age_code | label | count > > --+---+--- > > age_1| 30k | 1 > > age_1| 50k | 2 > > age_1| more | 2 > > age_2| 40k | 2 > > age_3| 40k | 1 > > > > I want the result to be: > > > > age_code | 30k | 40k | 50k | more > > --- > > age_1 | 1 | |2 | 1 > > age_2 | | 2 | > > age_3 | | 1 | | > > > > Is there any easy way to do the job or do I have to write a PL/pgSQL > > function? > > Got it: > # SELECT * FROM aaa; > age_code | label | count > --+---+--- > age_1| 30k | 1 > age_1| 50k | 2 > age_1| more | 2 > age_2| 40k | 2 > age_3| 40k | 1 > > --- > SELECT > s0.age_code, > (SELECT count FROM aaa s1 WHERE s1.age_code = s0.age_code AND s1.label = > '30k') as "30k", > (SELECT count FROM aaa s1 WHERE s1.age_code = s0.age_code AND s1.label = > '40k') as "40k", > (SELECT count FROM aaa s1 WHERE s1.age_code = s0.age_code AND s1.label = > '50k') as "50k", > (SELECT count FROM aaa s1 WHERE s1.age_code = s0.age_code AND s1.label = > 'more') as "more" > FROM aaa s0 > GROUP BY s0.age_code; > > age_code | 30k | 40k | 50k | more > --+-+-+-+-- > age_1| 1 | | 2 |2 > age_2| | 2 | | > age_3| | 1 | | > (3 rows) > > Alexander Dederer. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Why indexes are not used when scanning from functions?
The table (script followed) contains more than 20 records. When I directly selecting records by the single select statement select fio from patient_temp where fio like 'something%'; Postgres uses the index i have created and quickly returns the results. But if that query included into the function, slowest sequent scan starts. Is it a normal postgres behavior - not using indexes in functions? Or how to make indexes usable in functions? I have tried PL/pgSQL, and have got the same result. create table patient_temp( code serial primary key, fio varchar(80) ); create index fio_patient_temp_ind on patient_temp(fio); create function get_fio1(text) returns varchar as 'select fio from patient_temp where fio like $1::text || \'%\';' language 'sql'; Aidamir Lovpache <[EMAIL PROTECTED]> ---(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] Re: Transposing data
Hans-J?rgen Sch?nig wrote: > I want the values in column label to be displayed in the a-axis. Is > there an easy way to transform the data: > Here is the input data: > age_code | label | count > --+---+--- > age_1| 30k | 1 > age_1| 50k | 2 > age_1| more | 2 > age_2| 40k | 2 > age_3| 40k | 1 > > I want the result to be: > > age_code | 30k | 40k | 50k | more > --- > age_1 | 1 | |2 | 1 > age_2 | | 2 | > age_3 | | 1 | | > > Is there any easy way to do the job or do I have to write a PL/pgSQL > function? Got it: # SELECT * FROM aaa; age_code | label | count --+---+--- age_1| 30k | 1 age_1| 50k | 2 age_1| more | 2 age_2| 40k | 2 age_3| 40k | 1 --- SELECT s0.age_code, (SELECT count FROM aaa s1 WHERE s1.age_code = s0.age_code AND s1.label = '30k') as "30k", (SELECT count FROM aaa s1 WHERE s1.age_code = s0.age_code AND s1.label = '40k') as "40k", (SELECT count FROM aaa s1 WHERE s1.age_code = s0.age_code AND s1.label = '50k') as "50k", (SELECT count FROM aaa s1 WHERE s1.age_code = s0.age_code AND s1.label = 'more') as "more" FROM aaa s0 GROUP BY s0.age_code; age_code | 30k | 40k | 50k | more --+-+-+-+-- age_1| 1 | | 2 |2 age_2| | 2 | | age_3| | 1 | | (3 rows) Alexander Dederer. ---(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] Help with ECPG on debian Potato
Hi, I am running Debian GNU/Linux potato and I have a problem with ECPG: when I type the example provided in the PostgreSQL book: http://www.ca.postgresql.org/docs/aw_pgsql_book/node149.html I type : piou@yoda:~/BD$ ecpg truc.pgc and I have the following error : Error: Cannot open include file SQLCA in line 2 for information on Debian Systems the sqlca.h file is located in /usr/include/postgresql/sqlca.h I don't know what to do. Thanks in Advance. Lionel ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Transposing data
I want the values in column label to be displayed in the a-axis. Is there an easy way to transform the data: Here is the input data: age_code | label | count --+---+--- age_1| 30k | 1 age_1| 50k | 2 age_1| more | 2 age_2| 40k | 2 age_3| 40k | 1 I want the result to be: age_code | 30k | 40k | 50k | more --- age_1 | 1 | |2 | 1 age_2 | | 2 | age_3 | | 1 | | Is there any easy way to do the job or do I have to write a PL/pgSQL function? Hans ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Calculating the age of a person
Hans-Jürgen Schönig wrote:
> I have a table containing the birthdays of various persons. The target
> is to compute the age of a persons.
>
> persons=# SELECT age(birth), * FROM persons LIMIT 1;
> age | id | name | birth| gender |
> income
> ---+++++
>
> 31 years 4 mons 16 days 23:00 | 1 | Albert | 1970-01-01 | m |
> 35000
> (1 row)
>
> When I use age() I don't get full years. Is there an easy way to round
> ::reltime off or up without writing a function. Is there any possibility
> to use plain SQL only?
>
> Hans
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Hey Hans try usting date_part function select
date_part('year',birth)
Regards
--
Najm Hashmi
Tel:514-271-9791
www.mondo-live.com
www.flipr.com
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] PL/pgSQL problem with variables
I want to write a function that creates a new column for every value found in the table but something seems to go wrong with the variable: CREATE FUNCTION transp() RETURNS int4 AS ' DECLARE col RECORD; cname varchar; BEGIN CREATE TABLE tmp_trans (label) AS SELECT label FROM view_inc_age_1; FOR col IN SELECT DISTINCT age_code FROM view_inc_age_1 LOOP cname := col.age_code; ALTER TABLE tmp_trans ADD COLUMN cname int4; END LOOP; RETURN 0; END; ' LANGUAGE 'plpgsql'; persons=# SELECT transp(); ERROR: parser: parse error at or near "$1" The problem occurs in the line containing "ALTER TABLE". Does anybody know what is going wrong? Hans ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] RULES
On Tue, May 22, 2001 at 10:44:06AM +0300, Mart?n Marqu?s wrote: > On Mar 22 May 2001 20:19, J.Fernando Moyano wrote: > > Deleting is broken if it deletes more than one row. The rule is executed > > only one time each delete command, and not one time each deleted row. > > > > It's this OK ??? Am i wrong ??? > > It's a "feature" ?? ;-) > > That's the right behaviour! > Each time you do a delete, besides the delete, it will execute the update > If more then one row is deletes, update gets executed only once. > Rules are rewrites of the SQL query, before it gets planned and optimized, so they are good for things that need to happen once per statement. Triggers, on the other hand, fire per tuple, when the actual bits are going into/out of the underlying table. They're good for things that need to happen per tuple. See Bruce's book for the basics: http://www.ca.postgresql.org/docs/aw_pgsql_book/node166.html Ross ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] implied rows when a rule gets executed.
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 In a rule... is there some way of getting the number of rows updated by the query that activates this rule ?? Martín Marqués says: >You'll have to do more advance programing there. See if count(*) can help >you >in any way. >I see you have to update usadas=(usadas-1). Try something like: > >usadas=(usadas-(select count(*) from piezas WHERE condition_delete) ) ... but, how can i know "condition_delete" ??? If there is no way ... i will try with triggers ... but i think it's little simetrical . ==> i can get done my stuff with rules when inserting, but it isn't possible when deleting ??? Thanks . - -- Fernando Moyano Frase del día: - -- Fuerza tu OS/2 a OS/4!!. (*) SymeX ==> http://symex.lantik.com (*) WDBIL ==> http://wdbil.sourceforge.net (*) Informate sobre LINUX en http://www.linux.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.4 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE7C1eRoZaf9MvtDvcRAuqCAJ97HFg61+yJnRgn/Ulkd++paWTWRACfVBXQ RslGvE5sh1IBYO1s4P36nXo= =Tlk4 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] index/join madness
Ok, I've built the most insane query ever. It joins 11 tables, most of which are the same table, just extracting different values. Here is the query plan: Nested Loop (cost=0.00..5011.89 rows=1 width=156) -> Nested Loop (cost=0.00..4191.82 rows=1 width=140) -> Nested Loop (cost=0.00..4189.79 rows=1 width=112) -> Nested Loop (cost=0.00..4188.58 rows=1 width=104) -> Nested Loop (cost=0.00..4186.55 rows=1 width=88) -> Nested Loop (cost=0.00..3366.48 rows=1 width=72) -> Nested Loop (cost=0.00..2546.41 rows=1 width=68) -> Nested Loop (cost=0.00..1726.34 rows=1 width=52) -> Nested Loop (cost=0.00..906.27 rows=1 width=32) -> Seq Scan on formdata f6 (cost=0.00..904.16 rows=1 width=4) -> Index Scan using users_pkey on users u (cost=0.00..2.02 rows=1 width=28) SubPlan -> Seq Scan on sentletters (cost=0.00..0.00 rows=1 width=4) -> Seq Scan on formdata f5 (cost=0.00..818.42 rows=131 width=20) -> Seq Scan on formdata f2 (cost=0.00..818.42 rows=131 width=16) -> Seq Scan on formdata f1 (cost=0.00..818.42 rows=131 width=4) -> Seq Scan on formdata f3 (cost=0.00..818.42 rows=131 width=16) -> Index Scan using formmvl_pkey on formmvl m (cost=0.00..2.02 rows=1 width=16) -> Seq Scan on relations r (cost=0.00..1.12 rows=7 width=8) -> Index Scan using users_pkey on users u2 (cost=0.00..2.02 rows=1 width=28) -> Seq Scan on formdata f4 (cost=0.00..818.42 rows=131 width=16) If anyone has a screen wide enough to see this, you will see that the majority of the time is spent doing sequential scans on the formdata table. This table needs formid, fieldid and userid to find the value I'm looking for. It has one index defined on: Index "formdata_pkey" Attribute | Type ---+- formid| integer occid | integer userid| integer fieldid | integer unique btree (primary key) In my case I'm ignoring occid since it's always 1 for these values. Is there any way I can coerce this into using a multifield index? My joins generally look like: JOIN formdata AS f2 ON (u.id=f2.userid AND f2.formid=1 AND f2.fieldid=2) I'm a little curious as to why it's not using the primary key... Finally, I'm planning on moving this to 7.2 and converting all the joins to use outer joins. Will there be a significant penalty in performance running outer joins? -Michael _ http://fastmail.ca/ - Fast Free Web Email for Canadians ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
