[SQL] hiding pg messages in c programs
Hi, how can I disable the output of pg sql commands on the shell from within my C programs? Thanks, Markus ---(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] PL/pgsql question
hi, i define a function using pgacces with pl/pgsql. it seems that the queries define in the function wasn't executed. if I try to excute the same queries from the psql everything, really, works fine. the body of the function is: --- Declare rec_struttura record; estra_capo float8; estra_non float8; rand_doub float8; row float8; capo int; i int; rand_max float8; Begin i:=0; For rec_struttura in Select * From struttura_campione Loop i:=i+1; prov := rec_struttura.cod_prov; estra_capo := rec_struttura.num_capo; estra_non := rec_struttura.num_non_capo; capo := 1; row:=prova_rand(prov,capo,1)::float8; rand_doub:=(estra_capo/row); if i = 1 then create table tmp as select * from veneto where cod_prov=prov and capoluo=capo and rand < rand_doub and occupato = false limit estra_capo; end if; if i > 1 then insert into tmp select * from veneto where cod_prov=prov and capoluo=capo and rand < rand_doub and occupato = false limit estra_capo ; end if; insert into campione_out select note, prefisso, telefono1 as telefono, cap, cod_com, cod_prov, citta, nome_cogno as nome_cognome, capoluo, estrazione as ultimo_acesso, rand from tmp; insert into tab_estrazioni (prefisso,telefono) select prefisso as presisso, telefono1 as telefono from tmp; delete from tmp; rand_max:=prova_rand(prov,capo,2); update veneto set occupato = true, estrazione = now() where (capoluo = capo and cod_prov = prov) and (rand <= rand_max) and occupato=false ; end loop; return rand_doub; end; -- no errors are report in log file during the excution of this function. What should i change in the function? Thanks in advance for any advice Andrea P.S. i'm sorry for my bad english ---(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: php-nuke
Sharmad Naik wrote: > > hi, > I wanted to know that does postgresql database support php-nuke > It's more the other way around, does php-nuke support PostgreSQL. The answer: AFAIK they still have to build a dbms bridge for PostgreSQL. In Phorum for instance you have different bridges for different DBMS's. HTH, Nils Zonneveld ---(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: Distinct Values
I want a "dirty" solution try something like that. If you use "group by author" url must be in aggregate function. The authors are now DISTINCT. myjava=# SELECT author, max(url) FROM books group by author; author | max +- abc| dsafsdf cde| sdfdsf (2 rows) Hans ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Foreign key to base table with rows in inherited tables
I've declared a foreign key in table FileId referencing the primary key in Datafile. Datafile in itself does'nt have any rows, but Reservationfile, inheriting from Datafile has. I can't insert a row in FileId because no row is found in Datafile: this is not what I expected. I've tried suffixing Datafile with an asterisk (even though that shouldn't be necessary in 7.1), but that's not valid syntax. I'm running 7.1.release4 on Debian Woody. Table definitions for clarity: -- Has no rows of its own declare Datafile ( datafileNrnot nullprimary key ); -- This one has rows declare Reservationfile ( ) inherits(Datafile) ; declare FileId ( datafileNrnot null references Datafile ); ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] PL/PGSQL
Hi! I can't find any info about possible data types that can be returned from procedure. I want to return tuple. When I use OPAQUE as a return type I get error message that OPAQUE can be only returned from trigger. It is possible to do this without triggers? Code reviews are like sex, just anyone can do it, but skill and training can make you a lot better at it." - LJ Thomas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Cluster and primary key
I can't find a way to cluster a table on another index and keep it's primary key. When I define the table the primary key is created, but it's dropped when I cluster the table on another index. I've tried "alter table add constraint", but that's not supported for primary keys. I'm running 7.1.release4 on Debian Woody. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] cascading delete - recursivity
Hi, I have a table of categories (it's pseudo-sql...) : CATEGORIES ( id_category PRIMARY KEY, id_category_parent (FOREIGN KEY ? CONSTRAINT ??) , cat_text ) There is recursivity : id_category_parent is -1 (= no parent cat) or the id_category of a category in this table. What is the SQL statements to create such a table so that when you delete a category, all sub-categories are automatically deleted ? I've tried many things like CONSTRAINT... but the query fails ! Thanks ! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Re: Distinct Values
Nicolas schrieb: > Hi, > > I'm trying to retreive DISTINCT Values from a two colomn table called > "Books". The colomns are named "Author" and "URL". > DISTINCT values should be retieved from the "Author" Colomn , and then I > should be able to retrieve the corresponding URL. > How do I build the SQL Query ? > I tried to use: "SELECT DISTINCT Author FROM Books" But this does not allow > me to fetch the URL value on the other colomn. > > Thanks > Nicolas The problem is that one author may have multiple urls - if you want to select the url too, the database does not know which url to retrieve. Hans ---(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] audit trail and system catalogs
Hi,
we strongly need to implement an audit trail as a prerequisite for
clinical trials, that is a functionality which records any change of any
data item in a database into one single table containing these events.
I found that one could use rules for this, but this would require one rule
for each attribute of each table ("...ON UPDATE ... WHERE old.attr <>
new.attr...").
My first question:
Could one reduce the rule set with rules for tables, e. g. one rule which
fires whenever *some* attribute of a table changes? Would there be a
possibility to reference the changed attributes in the action clause?
Assuming that one really needs one rule for each attribute I would like to
create a script which generates all rules for all attributes of all tables
for a database.
The problem is, how to get the names of all tables and their attributes? I
looked into the system tables ("pg_*"), but there were many tables and
many attributes for *my* tables, and I did not figure out how to
distinguish my tables and my attributes from the other ones. None of the
columns in pg_class and pg_attribute seems to give information on wether
the item is system or user defined.
My second question:
How can I loop to all of *my* tables and *my* attributes, ignoring system
tables and system generated attributes within my tables?
Thank you very much for any hint,
Markus
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Re: rpt
In article <[EMAIL PROTECTED]>, "Hilkiah Lavinier" <[EMAIL PROTECTED]> wrote: > Hi, this is probably not the correct group for this post, but it's kinda > related. Does anyone know of a report writer/generator that works for > postgres? It would be nice if it is natively built in or some 3rd party > tool that I can use. Depends on what platform you want to run the report generator on. If you're happy with a Windows platform, you could connect Access or Crystal Reports, or a large number of other query/report tools to Pg via ODBC. -- Jeff Boes vox 616.226.9550 Database Engineer fax 616.349.9076 Nexcerpt, Inc. [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] multiple sql update w/ major time issues
I have a sql update statement that is dealing with hundreds of thousands of records. It takes hours and hours to complete (if it does complete and not take down the server). Here is how I have it right now: update aud_member_ext_attributes b set EXTVALUE217 = (select a.MTD from gl_totals a where a.category = 'tankrent' and a.CUST_NO = b.EXTVALUE101 and a.DIST_NO = b.EXTVALUE102 and a.SUB_NO = b.EXTVALUE105 and a.FUEL_TYPE = b.EXTVALUE123); update aud_member_ext_attributes b set EXTVALUE223 = (select a.YTD from gl_totals a where a.category = 'tankrent' and a.CUST_NO = b.EXTVALUE101 and a.DIST_NO = b.EXTVALUE102 and a.SUB_NO = b.EXTVALUE105 and a.FUEL_TYPE = b.EXTVALUE123); update aud_member_ext_attributes b set EXTVALUE229 = (select a.R12 from gl_totals a where a.category = 'tankrent' and a.CUST_NO = b.EXTVALUE101 and a.DIST_NO = b.EXTVALUE102 and a.SUB_NO = b.EXTVALUE105 and a.FUEL_TYPE = b.EXTVALUE123); There are 3 "extvaluexxx" that are set for each "category" for a total of 9 categories. This makes a grand total of 27 update statements. Any suggestions? It would be much appreciated! ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Please help! Functions passing records between them
Guys; I am begging for your help again. I can't find a solution to my problem. I am porting a complex system from Oracle to PostgreSQL and I need to implement the following: function 1 does some processing and returns a record (I can declare it as a row in a view) function 2 uses func1 to get that record and does some more processing My problem is that even if I can return a record from my function 1, function 2 does not read it properly Here is an example: create view my_view as select null as type, null as value, null as timestamp; -- this is how I "declare" the user-defined data structure (I could not find any other way) create function func1() returns my_view as ' declare my_record my_view%rowtype; begin . . my_record.type := ''AAA''; my_record.value := 25; my_record.timestamp := now(); -- this is for simplicity return my_record; end; ' LANGUAGE 'plpgsql'; create function func2() returns varchar as ' declare my_record my_view%rowtype; begin select func1() into my_record; return my_record.type; end; ' LANGUAGE 'plpgsql'; It compiles and runs fine, except that it does not return what it's supposed to. It gives me some strange huge number, which I assume is some kind of OID I know that there are quite a few gurus of PostgreSQL out there - please help me solve this problem. May be my whole approach is wrong, but I need to be able to accomplist this: pass some kind of user-defined structures between function Thank you so much for your help Alla Gribov ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Sub-SELECT uses un-GROUPed attribute: how to solve.
Hi all! I want to make a report of (let's say) object sales, and i'm getting the error: Sub-SELECT uses un-GROUPed. I think this will be better understand with an example: (this is a over-simplified one, but it gets the point). (See data model and data to fill it below.) If i have: SALES: --- Object | Datetime --- chair | 2001-01-15 chair | 2001-01-16 table | 2001-01-17 chair | 2001-02-15 table | 2001-02-16 I want to get something as follows: Object | Month | Sales | Total sales on that month -- chair | 01| 2 | 3 table | 01| 1 | 3 chair | 02| 1 | 2 table | 02| 1 | 2 well, if i do: select object, to_char(sale_date, 'MM') as Month, count(*) as Sales from sales group by to_char(sale_date, 'MM'), object; object | month | sales +---+--- chair | 01| 2 table | 01| 1 chair | 02| 1 table | 02| 1 It's Ok. If i do: select object, to_char(sale_date, 'MM') as Month, count(*) as Sales, (select count(*) from sales s2 where to_char(s2.sale_date, 'MM') = to_char(s1.sale_date, 'MM') ) as Total_Sales from sales s1 group by to_char(sale_date, 'MM'), object; ERROR: Sub-SELECT uses un-GROUPed attribute s1.sale_date from outer query I get that error. I can understand it, since in the subselect i'm using s1.sale_date that is not grouped yet. My question is: is there a way to get that result? I've though of using a function, so it woud be: select object, to_char(sale_date, 'MM') as Month, count(*) as Sales, total_for_month(sale_date, 'MM') as Total_Sales from sales s1 group by to_char(sale_date, 'MM'), object; But i'd like to do it without using functions because i would have to define a function for each different period i want to show results. (for example, to get year or quarter totals instead of months). I think this is a common query to report sales. Does anybody found a beautiful solution? Thanks for your attention. -- Gaizka Villate -- Data model for the example: create table sales ( object varchar(30), sale_date date ); copy sales from stdin using delimiters '|'; chair|2001-01-15 chair|2001-01-16 table|2001-01-17 chair|2001-02-15 table|2001-02-16 \. ; ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] which sql syntax is better for joins on pgsql?
Hi all...one question..which sql syntax for joins will perform better on pgsql.. something like xxx OUTER JOIN yyy or SELECT xxx,yyy FROM bbb,ccc WHERE bbb.xxx = ccc.yyy ?? Are there any performance differences in executing these to queries in pg or will they be the same in means of time and resources?? I'm asking this because i'm tranferring an access97 db to postgres and i am using passthrough queries from access which are written as the first one i listed.. Thanks to all! ---(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: update from another table
That doesn't tend to work in any SQL database I've ever used, though I'm told Informix can do something similiar. You need to insert the data into a temp table then delete the rows in the base table, then insert back from the temp table. Yeah it is a pain. John "ivan" <[EMAIL PROTECTED]> wrote in message 9fq8eq$1cpd$[EMAIL PROTECTED]">news:9fq8eq$1cpd$[EMAIL PROTECTED]... > dear sir, > > i would like to know how can i update a table with columns from another > table and adding a new column with a secuence, > > i have try > > update table > set column = (select column from table2), .., set column=secuence.. > > is it right? > > thanks > > ivan > > > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Getting row with id=max(id)
I'd like to retrieve a row of a table that has the maximum ID. For example, with: id | s +--- 1 | alpha 2 | beta 3 | gamma 4 | delta I'd like to get the row with ID=4. I've tried: SELECT * FROM mytable WHERE id=(SELECT MAX(id) FROM mytable); The subquery can take a /really/ long time on a table that is large. The query: SELECT * FROM mytable ORDER BY id DESC LIMIT 1; doesn't seem to help very much. What query is the fastest at getting this row? A related question is: is there a way to time a query in psql, like the client of MySQL does? ---(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] Integrity reference and inheritance
I wanted to build the following schema : - one "generic" document table with a column doc_id ; - a couple of "specific" document tables inheriting from doc ; - a table refering a document by it's id with and integrity constraint on it. In SQL : CREATE TABLE doc ( doc_id serial PRIMARY KEY, ); CREATE TABLE lexique ( ) INHERITS (doc); CREATE TABLE word_doc ( id serial PRIMARY KEY, doc_id int4 NOT NULL CONSTRAINT word_doc_doc_id_ref REFERENCES doc ON DELETE CASCADE, ); What I tried to do next is to insert a new "lexique" entry, and then a bunch of "word_doc" rows refering this entry. The last part fails with a constraint violation this is quite normal since the trigger in backend/utils/adt/ri_triggers.c makes a "SELECT FROM ONLY". What should I do ? Should I consider another way to do what I want, rewrite a trigger to replace the system one ? -- Le cinéma en Lumière : http://www.lumiere.org/ Fingerprint : 4721 651D D309 B302 93E4 5D73 CC52 DF3A E7C7 86CF ---(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] rpt
Hi, this is probably not the correct group for this post, but it's kinda related. Does anyone know of a report writer/generator that works for postgres? It would be nice if it is natively built in or some 3rd party tool that I can use. Would appreciate any info. regards, Hilkiah ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Bit Mapping operation
Hi all, I am just wondering if bit map operations are possible in pl/pgsql. We are storing sixteen different (one or many ... i.e. check boxes ) choices in a small int. For data cruchinging reasons, we need to reverse the process to find out what what fields where selected or checked. Is this info somewhere in the docs? Thanks in advance. Regards, -- Najm Hashmi Tel:514-271-9791 www.mondo-live.com www.flipr.com
[SQL] sum of string columns, why ?
I have one table with thwo columns, user and text, why sum all the text fields of the one user ? i need this. thank you Marcos ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] need help please on triggers..
Hi, How to create a foreign key in postgresql ? need triggers ? Thanks, Fred ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Help datetime
i have problem in datetime
my_table (start_date datetime,stop_date datetime);
insert into my_table values
('Sun May 13 16:19:27 2001 JAVT','Sun May 13 14:06:13 2001 JAVT')
select stop_date-start_date from my_table
the result is "@ 2 hours 13 mins 14 secs"
but i want result in minutes(integer) not interval
have idea ??
plz help me
thanks
Nizomi
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] update from another table
dear sir, i would like to know how can i update a table with columns from another table and adding a new column with a secuence, i have try update table set column = (select column from table2), .., set column=secuence.. is it right? thanks ivan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Function returning record
Is it possible in PostgreSQL to write a function that would return a
record type.
What I need is something like this:
create function my_func(varchar)
return record as '
declare
my_rec record;
begin
select null as field1, null as field2
into my_rec;
some processing to populate the actual values of the record
return my_rec;
end;
' LANGUAGE 'plpgsql';
I get the following when I try to compile this:
NOTICE: ProcedureCreate: return type 'record' is only a shell
and following when I try to execute it (even though I am not sure how
to execute this at all);
ERROR: fmgr_info: function 0: cache lookup failed
Please help.
Thanks a lot in advance
Alla
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] Re: multiple sql update w/ major time issues
Dawn schrieb: > I have a sql update statement that is dealing with hundreds of > thousands of records. It takes hours and hours to complete (if it > does complete and not take down the server). Here is how I have it > right now: > > update aud_member_ext_attributes b > set EXTVALUE217 = > (select a.MTD > from gl_totals a > where a.category = 'tankrent' > and a.CUST_NO = b.EXTVALUE101 > and a.DIST_NO = b.EXTVALUE102 > and a.SUB_NO = b.EXTVALUE105 > and a.FUEL_TYPE = b.EXTVALUE123); > > update aud_member_ext_attributes b > set EXTVALUE223 = > (select a.YTD > from gl_totals a > where a.category = 'tankrent' > and a.CUST_NO = b.EXTVALUE101 > and a.DIST_NO = b.EXTVALUE102 > and a.SUB_NO = b.EXTVALUE105 > and a.FUEL_TYPE = b.EXTVALUE123); > > update aud_member_ext_attributes b > set EXTVALUE229 = > (select a.R12 > from gl_totals a > where a.category = 'tankrent' > and a.CUST_NO = b.EXTVALUE101 > and a.DIST_NO = b.EXTVALUE102 > and a.SUB_NO = b.EXTVALUE105 > and a.FUEL_TYPE = b.EXTVALUE123); > > There are 3 "extvaluexxx" that are set for each "category" for a total > of 9 categories. This makes a grand total of 27 update statements. > Any suggestions? It would be much appreciated! Is there no way to do it in one statement? Try something like that: update aud_member_ext_attributes b set EXTVALUE223 = (select a.YTD from gl_totals a where a.category = 'tankrent' and a.CUST_NO = b.EXTVALUE101 and a.DIST_NO = b.EXTVALUE102 and a.SUB_NO = b.EXTVALUE105 and a.FUEL_TYPE = b.EXTVALUE123), EXTVALUE229 = (select a.R12 from gl_totals a where a.category = 'tankrent' and a.CUST_NO = b.EXTVALUE101 and a.DIST_NO = b.EXTVALUE102 and a.SUB_NO = b.EXTVALUE105 and a.FUEL_TYPE = b.EXTVALUE123); You can update multiple columns with just one update. Hans ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] cascading delete - recursivity
On Sat, 9 Jun 2001, Postgresql wrote: > Hi, > > I have a table of categories (it's pseudo-sql...) : > > CATEGORIES > ( > id_category PRIMARY KEY, > id_category_parent (FOREIGN KEY ? CONSTRAINT ??) , > cat_text > ) > > There is recursivity : id_category_parent is -1 (= no parent cat) or the > id_category of a category in this table. > What is the SQL statements to create such a table so that when you delete a > category, all sub-categories > are automatically deleted ? > I've tried many things like CONSTRAINT... but the query fails ! If you want to use foreign keys, using -1 for no cat is going to mean you're going to need a -1 category. You're probably better off using NULL for that. you probably want something like: id_category_parent REFERENCES categories ON DELETE CASCADE [you may want to add ON UPDATE CASCADE] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] listing foreign keys
is there any way to view the existing foreign keys in a database schema? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Do I have to lock table in this case?
I have a table like this: id primary key unique I need to do: - if 'unique' exists in table, return appropriate id else insert into table new id. Now doing: select id where unique=%s insert/update creates a race condition. The application can theoretically rollback & redo the whole transaction (not simple in some cases). What is the preferred solution to this problem (I got about 3 such cases in one simple application). Ondrej -- It is better to remain childless than to father an orphan. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Foreign key to base table with rows in inherited tables
Yes, you cannot currently do this. Check past discussions on mailing lists for more details. Inheritance and constraints is pretty dodgy right now (for instance, Reservationfile.datafileNr is not unique). On Tue, 5 Jun 2001, Mikael Kjellström wrote: > I've declared a foreign key in table FileId referencing the primary key in > Datafile. Datafile in itself does'nt have any rows, but Reservationfile, > inheriting from Datafile has. > > I can't insert a row in FileId because no row is found in Datafile: this is > not what I expected. > > I've tried suffixing Datafile with an asterisk (even though that shouldn't > be necessary in 7.1), but that's not valid syntax. > > I'm running 7.1.release4 on Debian Woody. > > Table definitions for clarity: > > -- Has no rows of its own > declare Datafile ( > datafileNr not nullprimary key > ); > -- This one has rows > declare Reservationfile ( > ) > inherits(Datafile) > ; > declare FileId ( > datafileNr not null > references Datafile > ); ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] rpt
Hilkiah, you could use any report writing tool which connects to databases via ODBC. Download the Windows ODBC driver and install it. Set the '-i' flag on the postmaster, set up the ODBC interface to PostGreSQL in Windows and you're ready to fly :). Best regards, Chris - Original Message - From: "Hilkiah Lavinier" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, June 08, 2001 7:32 AM Subject: [SQL] rpt > Hi, this is probably not the correct group for this post, but it's kinda > related. Does anyone know of a report writer/generator that works for > postgres? It would be nice if it is natively built in or some 3rd party > tool that I can use. > > Would appreciate any info. > > regards, > Hilkiah > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Re: UPDATE with concatenate
[EMAIL PROTECTED] (Laurent Patureau) schreef: >UPDATE table SET col .= '$val' Try: UPDATE table set col = col || '$val' http://www.postgresql.org/idocs/index.php?functions-string.html -- Vriendelijke groet, René Pijlman <[EMAIL PROTECTED]> Wat wil jij leren? http://www.leren.nl/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Getting row with id=max(id)
> A related question is: is there a way to time a query in psql, like the > client of MySQL does? use the explain commmand explain select * from foo; > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [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
Re: [SQL] PL/PGSQL
On Tue, Jun 05, 2001 at 08:18:03AM +0200, Andrzej Roszkowski wrote: > Hi! > I can't find any info about possible data types that can be returned from > procedure. I want to return tuple. When I use OPAQUE as a return type I > get error message that OPAQUE can be only returned from trigger. It is > possible to do this without triggers? A little RTFM'ing wouldn't hurt. Currently you can't return tuples from "regular" PL/pgSQL functions. It heard this functionality will make it in 7.2. -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer Death to all fanatics!!! ---(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: Bit Mapping operation
In 7.1 it's there... using the |(or) &(and), etc.. operators. In 7.0.3 it's not although can be added easily. example: test=> select 2 | 1; ?column? -- 3 (1 row) test=> select 2 & 1; ?column? -- 0 (1 row) test=> select 3 & 1; ?column? -- 1 (1 row) test=> On Mon, 11 Jun 2001, Najm Hashmi wrote: > Hi all, I am just wondering if bit map operations are possible in > pl/pgsql. We are storing sixteen different (one or many ... i.e. check > boxes ) choices in a small int. For data cruchinging reasons, we need > to reverse the process to find out what what fields where selected or > checked. Is this info somewhere in the docs? > Thanks in advance. > 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] Inheritance - Multiple membership
This question was asked in 1998 on this list by someone else, but did not appear to be answered... Say I have a tbl_person. tbl_teacher and tbl_student both inherit from tbl_person and each have their own extended attributes. Is it possible for a person in tbl_person to be both a teacher at the school, and also a student (say night school or something). Also, the student may be a part-time employee, like a graduate student. If it is possible, what would an insert look like. Given the possibility that someone can exist in both tbl_teacher and tbl_student, what happens if you delete their student record. I'm guessing that it should leave the other records in tact. Any ideas? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] multiple sql update w/ major time issues
[EMAIL PROTECTED] (Dawn) writes: > I have a sql update statement that is dealing with hundreds of > thousands of records. It takes hours and hours to complete (if it > does complete and not take down the server). No wonder, if you write it like that. You're forcing a fresh select from gl_totals for each row of aud_member_ext_attributes; then you're doing it again for each UPDATE command. If you were using Postgres (which I surmise you are not, because it doesn't accept aliases in UPDATE) you could do update aud_member_ext_attributes set EXTVALUE217 = gl_totals.MTD, EXTVALUE223 = gl_totals.YTD, EXTVALUE229 = gl_totals.R12 where gl_totals.category = 'tankrent' and gl_totals.CUST_NO = EXTVALUE101 and gl_totals.DIST_NO = EXTVALUE102 and gl_totals.SUB_NO = EXTVALUE105 and gl_totals.FUEL_TYPE = EXTVALUE123; regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Integrity reference and inheritance
Sorry for the double post, I messed up in my addresses. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Cluster and primary key
On Tue, Jun 05, 2001 at 07:51:11AM +, Mikael Kjellström allegedly wrote: > I can't find a way to cluster a table on another index and keep it's > primary key. > > When I define the table the primary key is created, but it's dropped when I > cluster the table on another index. > > I've tried "alter table add constraint", but that's not supported for > primary keys. > > I'm running 7.1.release4 on Debian Woody. That can't be helped. The current cluster implementation drops all indexes on the table you're clustering, with the exception of the index you're clustering on. You could work around this limitation in the current cluster implementation by recreating the index enforcing the primary key. I hope this is of help to you, Mathijs -- "A book is a fragile creature. It suffers the wear of time, it fears rodents, the elements, clumsy hands." Umberto Eco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Are SQL commands "atomic" ?
I'm using 7.1.1 right now, and have the following table:
id | s
+---
1 | alpha
2 | beta
3 | gamma
4 | delta
(4 rows)
I'd like to switch the id of "beta" to 3 and the id of "gamma" to 2 ("flip"
them). Since id is the PK, it must remain unique and so I can't just set
the two lines using two UPDATEs.
My solution is:
UPDATE t1 SET id=id#1 WHERE id=2 OR id=3; -- # is the XOR operator
where 2#1=3 and 3#1=2. One statement will change both values as I want. But
when I run the statement, the server replies with:
ERROR: Cannot insert a duplicate key into unique index t1_pkey
If the statement is "atomic", then if the statement succeeds, the IDs will
be unique and the error is incorrect. Does this imply that SQL statements
are not actually atomic?
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
[SQL] How to store a linked list in a RDBMS
I have a linked list representing a tree. How do I store it in the database? Does PGSQL give us any special procedures to deal with such structures? __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] behavior of ' = NULL' vs. MySQL vs. Standards
Interesting - my experience is that Access, at least, generally treats
NULL's correctly:
(This was done under Access 2000):
create table foo (name text(20))
insert into foo values ("bar");
insert into foo values ("bar");
insert into foo values ("bar");
insert into foo values ("bar");
insert into foo values (NULL);
insert into foo values (NULL);
insert into foo values (NULL);
insert into foo values (NULL);
select count(*) from foo where name=NULL;
returns 0
select count(*) from foo where name is null;
returns 4
select count(*) from foo where name <> "bar";
returns 0
Cheers,
Andy
-
Andrew J. Perrin - Assistant Professor of Sociology
University of North Carolina, Chapel Hill
269 Hamilton Hall CB#3210, Chapel Hill, NC 27599-3210 USA
[EMAIL PROTECTED] - http://www.unc.edu/~aperrin
On Thu, 7 Jun 2001, Mark Stosberg wrote:
> Stephan Szabo wrote:
> >
> > On Wed, 6 Jun 2001, Tom Lane wrote:
> >
> > > Stephan Szabo <[EMAIL PROTECTED]> writes:
> > > > Yes, column = NULL should *never* return true according to the spec (it
> > > > should always return NULL in fact as stated). The reason for breaking
> > > > with the spec is AFAIK to work with broken microsoft clients that seem to
> > > > think that =NULL is a meaningful test and generate queries using that.
>
> > I'd rather have the default be the spec correct behavior
> > and let people configure their server to follow the misinterpretation.
>
> I like that idea as well. Someone like me who didn't know that this
> feature was in there for M$ could have assumed it _was_ standard
> behavior, and started using it as a habit. Then when I started porting
> my code to another database, I'd have an extra surprise in for me. :)
>
> Rather than being an option targeted at just this piece of grammer,
> perhaps it could a piece of a potentially larger option of "stricter
> standards compliance." I realize there are a number of useful extensions
> to the SQL standard in Postgres (which I like and use.), but it seems
> like there would be uses for minimizing non-standard behavior, as well.
>
> Thank you all for your contributions to Postgres-- I use it everyday. :)
>
> -mark
>
> http://mark.stosberg.com/
>
> ---(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
>
---(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
