Re: [GENERAL] serial
Scott Marlowe, 04.12.2008 16:34: Like an Oracle DBA who told me pgsql was broken because the output of group by wasn't properly ordered like it was on Oracle 9. Then he did not know Oracle very well :) Even Oracle 9 does not guarantee that the output of an GROUP BY is actually sorted, with Oracle 10 this has just gotton more obvious because the grouping strategies were enhanced. Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] serial
On Fri, Dec 5, 2008 at 8:24 AM, Thomas Kellerer [EMAIL PROTECTED] wrote: Scott Marlowe, 04.12.2008 16:34: Like an Oracle DBA who told me pgsql was broken because the output of group by wasn't properly ordered like it was on Oracle 9. Then he did not know Oracle very well :) Even Oracle 9 does not guarantee that the output of an GROUP BY is actually sorted, with Oracle 10 this has just gotton more obvious because the grouping strategies were enhanced. yeah, that sounds merely like his short experience, or by-product of the way GROUP BY was implemented in some particular version of oracle 9, not a guaranteed outcome. Poor guy... he probably still lives on thinking that oracle is so good because of those little 'things' :) -- GJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Updatable Views - DEFAULT doesn't inherit from table???
Hi,
I have problems with inserting rows into an updatable view through it's
insert rule.
Running this:
insert into view_products_1
(id,firmid,name_en,name_hu,artnum1,artnum2,description_hu,description_en,pkgunitid,minpkg,customstariff,vat)
values
('23','1','','','dd','','d','dd','1','10','200','20')
the engine sends this error:
ERROR: null value in column qtyunitid violates not-null constraint
** Error **
ERROR: null value in column qtyunitid violates not-null constraint
SQL state: 23502
But in the table definition I defined DEFULT=(-1) for this field. What's
going wrong? Shouldn't it inherit these settings from the table?
Many thanks,
--
Best Regards,
Csaba Együd
IN-FO Studio
Here is the table:
---
CREATE TABLE whm.products
(
id serial NOT NULL,
firmid integer NOT NULL,
name_en character varying(250) NOT NULL DEFAULT ''::character varying,
name_hu character varying(250) NOT NULL DEFAULT ''::character varying,
artnum1 character varying(250) NOT NULL,
artnum2 character varying(250) NOT NULL DEFAULT ''::character varying,
description_hu character varying(512) NOT NULL DEFAULT ''::character
varying,
createtime timestamp with time zone NOT NULL DEFAULT now(),
createuser name NOT NULL DEFAULT session_user(),
lastmodtime timestamp with time zone NOT NULL DEFAULT now(),
lastmoduser name NOT NULL DEFAULT session_user(),
description_en character varying(512) NOT NULL DEFAULT ''::character
varying,
qtyunitid integer NOT NULL DEFAULT (-1),
pkgunitid integer NOT NULL DEFAULT (-1),
minpkg integer NOT NULL DEFAULT 0,
customstariff character varying(64) NOT NULL DEFAULT ''::character
varying,
vat numeric NOT NULL DEFAULT 20,
service boolean NOT NULL DEFAULT false,
notes character varying(512) DEFAULT ''::character varying,
CONSTRAINT pk_products_id PRIMARY KEY (id),
CONSTRAINT fk_products_firmid FOREIGN KEY (firmid) REFERENCES whm.firms
(id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT fk_products_qtyunitid FOREIGN KEY (qtyunitid) REFERENCES
whm.qtyunits (id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT products_pkgunitid FOREIGN KEY (pkgunitid) REFERENCES
whm.pkgunits (id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (OIDS=FALSE);
And here is the definition of the view:
CREATE OR REPLACE VIEW whm.view_products_1 AS
SELECT products.id, products.firmid, products.name_en, products.name_hu,
products.artnum1, products.artnum2, products.description_hu,
products.createtime, products.createuser, products.lastmodtime,
products.lastmoduser, products.description_en, products.qtyunitid,
products.pkgunitid, products.minpkg, products.customstariff, products.vat,
products.service, products.notes FROM whm.products WHERE products.firmid =
1;
CREATE OR REPLACE RULE view_products_1_insert AS
ON INSERT TO whm.view_products_1 DO INSTEAD INSERT INTO whm.products
(firmid, name_en, name_hu, artnum1, artnum2, description_hu, description_en,
qtyunitid, pkgunitid, minpkg, customstariff, vat, service, notes)
VALUES (1, new.name_en, new.name_hu, new.artnum1, new.artnum2,
new.description_hu, new.description_en, new.qtyunitid, new.pkgunitid,
new.minpkg, new.customstariff, new.vat, new.service, new.notes);
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Updatable Views - DEFAULT doesn't inherit from table???
maybe that constraint ?? CONSTRAINT fk_products_qtyunitid FOREIGN KEY
(qtyunitid) REFERENCES whm.qtyunits (id) MATCH FULL ON UPDATE
CASCADE ON DELETE CASCADE,
Also, that table seem to be far away from perfect, too many fields,
you should chop it into few smaller tables.
2008/12/5 Csaba Együd [EMAIL PROTECTED]:
Hi,
I have problems with inserting rows into an updatable view through it's
insert rule.
Running this:
insert into view_products_1
(id,firmid,name_en,name_hu,artnum1,artnum2,description_hu,description_en,pkgunitid,minpkg,customstariff,vat)
values
('23','1','','','dd','','d','dd','1','10','200','20')
the engine sends this error:
ERROR: null value in column qtyunitid violates not-null constraint
** Error **
ERROR: null value in column qtyunitid violates not-null constraint
SQL state: 23502
But in the table definition I defined DEFULT=(-1) for this field. What's
going wrong? Shouldn't it inherit these settings from the table?
Many thanks,
--
Best Regards,
Csaba Együd
IN-FO Studio
Here is the table:
---
CREATE TABLE whm.products
(
id serial NOT NULL,
firmid integer NOT NULL,
name_en character varying(250) NOT NULL DEFAULT ''::character varying,
name_hu character varying(250) NOT NULL DEFAULT ''::character varying,
artnum1 character varying(250) NOT NULL,
artnum2 character varying(250) NOT NULL DEFAULT ''::character varying,
description_hu character varying(512) NOT NULL DEFAULT ''::character
varying,
createtime timestamp with time zone NOT NULL DEFAULT now(),
createuser name NOT NULL DEFAULT session_user(),
lastmodtime timestamp with time zone NOT NULL DEFAULT now(),
lastmoduser name NOT NULL DEFAULT session_user(),
description_en character varying(512) NOT NULL DEFAULT ''::character
varying,
qtyunitid integer NOT NULL DEFAULT (-1),
pkgunitid integer NOT NULL DEFAULT (-1),
minpkg integer NOT NULL DEFAULT 0,
customstariff character varying(64) NOT NULL DEFAULT ''::character varying,
vat numeric NOT NULL DEFAULT 20,
service boolean NOT NULL DEFAULT false,
notes character varying(512) DEFAULT ''::character varying,
CONSTRAINT pk_products_id PRIMARY KEY (id),
CONSTRAINT fk_products_firmid FOREIGN KEY (firmid) REFERENCES whm.firms
(id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT fk_products_qtyunitid FOREIGN KEY (qtyunitid) REFERENCES
whm.qtyunits (id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT products_pkgunitid FOREIGN KEY (pkgunitid) REFERENCES
whm.pkgunits (id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (OIDS=FALSE);
And here is the definition of the view:
CREATE OR REPLACE VIEW whm.view_products_1 AS
SELECT products.id, products.firmid, products.name_en, products.name_hu,
products.artnum1, products.artnum2, products.description_hu,
products.createtime, products.createuser, products.lastmodtime,
products.lastmoduser, products.description_en, products.qtyunitid,
products.pkgunitid, products.minpkg, products.customstariff, products.vat,
products.service, products.notes FROM whm.products WHERE products.firmid =
1;
CREATE OR REPLACE RULE view_products_1_insert AS
ON INSERT TO whm.view_products_1 DO INSTEAD INSERT INTO whm.products
(firmid, name_en, name_hu, artnum1, artnum2, description_hu, description_en,
qtyunitid, pkgunitid, minpkg, customstariff, vat, service, notes)
VALUES (1, new.name_en, new.name_hu, new.artnum1, new.artnum2,
new.description_hu, new.description_en, new.qtyunitid, new.pkgunitid,
new.minpkg, new.customstariff, new.vat, new.service, new.notes);
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
GJ
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Updatable Views - DEFAULT doesn't inherit from table???
Csaba Együd wrote: Hi, I have problems with inserting rows into an updatable view through it's insert rule. [snip] But in the table definition I defined DEFULT=(-1) for this field. What's going wrong? Shouldn't it inherit these settings from the table? Maybe, but it doesn't (and I think I've seen someone arguing it shouldn't). You can manually apply constraints/defaults etc. to the view though. I can't recall if you do ALTER VIEW view_products_1 or ALTER TABLE view_products_1, but it's one of them. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Executing a user created function twice give an error
Thanks for all of you help. It's working now :-) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Resp.: [GENERAL] Automatic insert statement generator?
2008/12/4, Rob Richardson [EMAIL PROTECTED]: ... The problem, of course, is that the inventory table has a unique key constraint that gets violated. So, to do this, I'm going to have to write an insert query that lists every field in this table (all 62 of them), except for the primary key, which I'll have to force to something I know is unique. I would like a database function that would generate a string that would be a concatenation of all fields in a given table. Then, I could use the resulting string as the starting point for building an insert statement that will avoid the key field(s). Try: CREATE OR REPLACE FUNCTION list_fields(text) RETURNS text AS $BODY$ -- all attributes names, except those belonging primary key SELECT array_to_string( ARRAY(SELECT pa.attname FROM pg_attribute pa JOIN pg_class pc ON (pa.attrelid = pc.oid) WHERE pc.relname = $1 AND pa.attnum 0 AND pa.attnum ALL ((SELECT pco.conkey FROM pg_constraint pco WHERE pco.conrelid = pa.attrelid AND pco.contype = 'p')::smallint[])), ','); $BODY$ LANGUAGE SQL STABLE; Osvaldo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] IDLE in transaction - safest way to kill
Hi all Could anyone tell me what's the best thing to with idle transactions that are holding locks? I just killed the process as I wanted to get on with some work. I'm just not sure this is a good idea when we go into production. Cheers Will T -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Updatable Views - DEFAULT doesn't inherit from table???
Richard Huxton [EMAIL PROTECTED] a következőket írta üzenetében news:[EMAIL PROTECTED] Csaba Együd wrote: Hi, I have problems with inserting rows into an updatable view through it's insert rule. [snip] But in the table definition I defined DEFULT=(-1) for this field. What's going wrong? Shouldn't it inherit these settings from the table? Maybe, but it doesn't (and I think I've seen someone arguing it shouldn't). You can manually apply constraints/defaults etc. to the view though. I can't recall if you do ALTER VIEW view_products_1 or ALTER TABLE view_products_1, but it's one of them. -- Richard Huxton Archonet Ltd Richard, Thx for your reply. Is there any possible way to generate an sql to copy these defaults to the view. --Csaba -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Updatable Views - DEFAULT doesn't inherit from table???
Grzegorz Jaśkiewicz [EMAIL PROTECTED] a következőket írta üzenetében news:[EMAIL PROTECTED] maybe that constraint ?? CONSTRAINT fk_products_qtyunitid FOREIGN KEY (qtyunitid) REFERENCES whm.qtyunits (id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE, Also, that table seem to be far away from perfect, too many fields, you should chop it into few smaller tables. 2008/12/5 Csaba Együd [EMAIL PROTECTED]: Hi, thx for your reply too. Not that I guess because there is a default row in qtyunits with id=-1. Too many fields: How would you chop this table? thx, -- Csaba -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Planner picking topsey turvey plan?
Hi people, Does anyone know how I can change what I'm doing to get pgsql to pick a better plan? I'll explain what I've done below but please forgive me if I interpret the plans wrong as I try to describe, I've split it into 4 points to try and ease the mess of pasting in the plans.. 1) I've created a view orders that joins two tables credit and mult_ord together as below: CREATE VIEW orders AS SELECT b.mult_ref, a.show, MIN(a.transno) AS lead_transno, COUNT(a.transno) AS parts, SUM(a.tickets) AS items, SUM(a.value) AS value FROM (credit a LEFT OUTER JOIN mult_ord b ON a.transno = b.transno) GROUP BY b.mult_ref, a.show; 2) And an explain on that view comes out as below, it's using the correct index for the field show on credit which doesn't look too bad to me: DB=# explain select a.artist, a.date, b.mult_ref, b.items, b.parts from (show a inner join orders b on a.code = b.show) where b.show = 357600; QUERY PLAN Nested Loop (cost=15050.79..15099.68 rows=1013 width=70) - Index Scan using show_index01 on show a (cost=0.00..8.37 rows=1 width=26) Index Cond: (code = 357600::numeric) - HashAggregate (cost=15050.79..15071.05 rows=1013 width=39) - Nested Loop Left Join (cost=0.00..15035.60 rows=1013 width=39) - Index Scan using credit_index04 on credit a (cost=0.00..4027.30 rows=1013 width=31) Index Cond: (show = 357600::numeric) - Index Scan using mult_ord_index02 on mult_ord b (cost=0.00..10.85 rows=1 width=17) Index Cond: (a.transno = b.transno) (9 rows) 3) Then I have a table called show that is indexed on the artist field, and a plan for listing the shows for an artist is as below, again this doesn't look too bad to me, as it's using the index on artist. DB=# explain select * from show where artist = 'ALKALINE TRIO'; QUERY PLAN - Bitmap Heap Scan on show (cost=9.59..582.41 rows=153 width=348) Recheck Cond: ((artist)::text = 'ALKALINE TRIO'::text) - Bitmap Index Scan on show_index07 (cost=0.00..9.56 rows=153 width=0) Index Cond: ((artist)::text = 'ALKALINE TRIO'::text) (4 rows) 4) So.. I guess I can join show - orders, expecting an index scan on show for the artist, then an index scan on orders for each show. However it seems the planner has other ideas, it just looks backwards to me: DB=# explain select a.artist, a.date, b.mult_ref, b.items, b.parts from (show a inner join orders b on a.code = b.show) where artist = 'ALKALINE TRIO'; QUERY PLAN Hash Join (cost=1576872.96..1786175.37 rows=1689 width=70) Hash Cond: (a.show = a.code) - GroupAggregate (cost=1576288.64..1729424.39 rows=4083620 width=39) - Sort (cost=1576288.64..1586497.69 rows=4083620 width=39) Sort Key: b.mult_ref, a.show - Hash Left Join (cost=321406.05..792886.22 rows=4083620 width=39) Hash Cond: (a.transno = b.transno) - Seq Scan on credit a (cost=0.00..267337.20 rows=4083620 width=31) - Hash (cost=160588.80..160588.80 rows=8759380 width=17) - Seq Scan on mult_ord b (cost=0.00..160588.80 rows=8759380 width=17) - Hash (cost=582.41..582.41 rows=153 width=26) - Bitmap Heap Scan on show a (cost=9.59..582.41 rows=153 width=26) Recheck Cond: ((artist)::text = 'ALKALINE TRIO'::text) - Bitmap Index Scan on show_index07 (cost=0.00..9.56 rows=153 width=0) Index Cond: ((artist)::text = 'ALKALINE TRIO'::text) (15 rows) Any idea if I can get around this? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] IDLE in transaction - safest way to kill
select pg_cancel_backend(pid); --- On Fri, 5/12/08, William Temperley [EMAIL PROTECTED] wrote: From: William Temperley [EMAIL PROTECTED] Subject: [GENERAL] IDLE in transaction - safest way to kill To: pgsql-general@postgresql.org Date: Friday, 5 December, 2008, 2:08 PM Hi all Could anyone tell me what's the best thing to with idle transactions that are holding locks? I just killed the process as I wanted to get on with some work. I'm just not sure this is a good idea when we go into production. Cheers Will T -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Updatable Views - DEFAULT doesn't inherit from table???
Csaba Együd wrote: Thx for your reply. Is there any possible way to generate an sql to copy these defaults to the view. Nothing pre-packaged that I know of. You could probably do something copying values about in pg_attribute and pg_constraint, but that'd be an at your own risk sort of activity I suspect. For simpler defaults etc. you could probably get what you need from information_schema.columns -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Updatable Views - DEFAULT doesn't inherit from table???
2008/12/5 Csaba Együd [EMAIL PROTECTED]: Hi, thx for your reply too. Not that I guess because there is a default row in qtyunits with id=-1. Too many fields: How would you chop this table? I would generally try to normalize it. Queries are going to be bit more complicated than, but it would be easier to manage it, and extend. -- GJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Updatable Views - DEFAULT doesn't inherit from table???
Richard Huxton [EMAIL PROTECTED] a következőket írta üzenetében news:[EMAIL PROTECTED] Csaba Együd wrote: Thx for your reply. Is there any possible way to generate an sql to copy these defaults to the view. Nothing pre-packaged that I know of. You could probably do something copying values about in pg_attribute and pg_constraint, but that'd be an at your own risk sort of activity I suspect. For simpler defaults etc. you could probably get what you need from information_schema.columns -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Or use of coalesce() function??? I mean sg like: ..., qtyunitid = coalesce(NEW.qtyunitid, -1), ... in the Rule def. -- Csaba -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Updatable Views - DEFAULT doesn't inherit from table???
2008/12/5 Richard Huxton [EMAIL PROTECTED]: I can't recall if you do ALTER VIEW view_products_1 or ALTER TABLE view_products_1, but it's one of them. It seems odd, but adding defaults to a VIEW is done with ALTER TABLE. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] tuples
Hi, I have a question concerning psql. I found that psql has a defined command '-t' and that it turns off printing of column names and result row count footers, etc. what I look for, is a command, which would turn off result row count footer, but would print column names. is there an easy way to do this? regards, Matt Wirus Filipiński znowu atakuje http://klik.wp.pl/?adr=http%3A%2F%2Fprorocznia.pl%2Ff.html%3Fi%3D37160O-937882398O0sid=575 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] IDLE in transaction - safest way to kill
Could anyone tell me what's the best thing to with idle transactions that are holding locks? On Fri, Dec 5, 2008 at 2:25 PM, Glyn Astill [EMAIL PROTECTED] wrote: select pg_cancel_backend(pid); Thanks. Sorry for the basic question. Will -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Application Stack Builder - proxy error
Hello all, I know that I am WAY behind the times here, but I am just now getting to upgrade to 8.3. As I was reading through all of the installations I realized that in order to install postgis now, you have to use the Application Stack Builder and that this application has to access the internet. Is there a way around this? First off, I am having issues with connecting to the proxy. I can connect through my browser to the xml file, but not by using the application. I looked at my options and the internet browser is set to auto detect the proxy, which I am assuming is the problem. Now I can go around and find out the proper proxy and go through all kinds of hoops, but my second problem is that I have some clients that are not going to be able to do this because the servers that they load Postgres\postgis on do not have internet access and they will not expose them. So, how do I load postgis into Postgres without using Application Stack Builder; or how do I load it without internet connection? This is a huge issue for me and I would appreciate any help that anyone can provide. Thanks in advance.
Re: [GENERAL] Application Stack Builder - proxy error
On Fri, Dec 5, 2008 at 3:42 PM, Lee Keel [EMAIL PROTECTED] wrote: So, how do I load postgis into Postgres without using Application Stack Builder; or how do I load it without internet connection? This is a huge issue for me and I would appreciate any help that anyone can provide. You can grab the installer manually from http://pgfoundry.org/frs/?group_id=1000256release_id=1266 and copy that around in whatever way you need. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Application Stack Builder - proxy error
-Original Message- You can grab the installer manually from http://pgfoundry.org/frs/?group_id=1000256release_id=1266 and copy that around in whatever way you need. Thanks Dave! I will do that. I just wasn't sure if installing outside of Application Stack Builder was going to cause problems. Thanks again! Lee -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Application Stack Builder - proxy error
On Fri, Dec 5, 2008 at 3:56 PM, Lee Keel [EMAIL PROTECTED] wrote: -Original Message- You can grab the installer manually from http://pgfoundry.org/frs/?group_id=1000256release_id=1266 and copy that around in whatever way you need. Thanks Dave! I will do that. I just wasn't sure if installing outside of Application Stack Builder was going to cause problems. No - in fact it was intentionally designed so you could work that way if you wanted. StackBuilder just ties things together for those that want to use it. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] ALTER TABLE .....Error: Must be owner of the table
Hi, I had created some tables in the schema Foo and given GRANT ALL ON SCHEMA FOO TO SCOT; But when Scot tries to alter a table he gets the error MUST BE OWNER OF THE TABLE How can I give the ALTER permission or is there any other way to let other users modify or add tables in this schema? Thanks Josh
Re: [GENERAL] Favorite Tom Lane quotes
On Thu, 2008-12-04 at 10:47 -0500, Robert Treat wrote: http://archives.postgresql.org/pgsql-hackers/2006-04/msg00288.php I remember after reading this post wondering whether Tom uses caffeinated soap My RHCE course book is still at WC -- I read it on some days ;) -- Devrim GÜNDÜZ, RHCE devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org signature.asc Description: This is a digitally signed message part
Re: [GENERAL] IDLE in transaction - safest way to kill
On Fri, Dec 5, 2008 at 11:25 PM, Glyn Astill [EMAIL PROTECTED] wrote: select pg_cancel_backend(pid); No, pg_cancel_backend() cancels only *query*, and doesn't kill idle in transaction. I think that killing the backend (idle in transaction) with SIGTERM is better way. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL]
Dear all,
I create a stored function to select some row and return it as cursor.
the function receive some parameter that later I put it in where clause
But I found that select statement runs Case Sensitive
so if I have a record, for example
- id -nama_pelanggan
- 1 - Wira
I wil get the result if I call it this way : select
penggunaselect('mycursor', 'Wi');
but I don't got any row if I call it like this : select
penggunaselect('mycursor', 'Wi');
I need the search to run in not-case-sensitive mode
so please if you have any suggestion
Thank you
Anyway, this is the code
CREATE OR REPLACE FUNCTION penggunaselect(curs refcursor, pnama
character varying, pgrup character varying, paktif integer)
RETURNS refcursor AS
$BODY$
declare
pid_grup integer := 0;
sqltext character varying = '';
begin
-- cari id grup
if pgrup '' then
select id into pid_grup from grup where nama_grup = pgrup;
end if;
sqltext = 'select p.nama_pengguna, g.nama_grup, p.login_terakhir,
p.mulai_dibuat, p.aktif, p.id
from pengguna p, grup g
where p.id_grup = g.id';
if pnama '' then
sqltext = sqltext || ' and p.nama_pengguna like ''%' || pnama
|| '%'' ';
end if;
if pid_grup 0 then
sqltext = sqltext || ' and p.id_grup = ' || pid_grup;
end if;
if paktif = 1 then
sqltext = sqltext || ' and p.aktif = true';
end if;
if paktif = 2 then
sqltext = sqltext || ' and p.aktif = false';
end if;
sqltext = sqltext || ' order by p.nama_pengguna';
open curs for execute(sqltext);
return curs;
end;
$BODY$
LANGUAGE 'plpgsql';
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL]
On Fri, Dec 5, 2008 at 9:11 PM, hendra kusuma [EMAIL PROTECTED] wrote:
Dear all,
I create a stored function to select some row and return it as cursor.
the function receive some parameter that later I put it in where clause
But I found that select statement runs Case Sensitive
so if I have a record, for example
- id -nama_pelanggan
- 1 - Wira
I wil get the result if I call it this way : select
penggunaselect('mycursor', 'Wi');
but I don't got any row if I call it like this : select
penggunaselect('mycursor', 'Wi');
I need the search to run in not-case-sensitive mode
Change like to ilike
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] select return case sensitive record
Sorry, forgot to add subject
On 12/5/08, hendra kusuma [EMAIL PROTECTED] wrote:
Dear all,
I create a stored function to select some row and return it as cursor.
the function receive some parameter that later I put it in where clause
But I found that select statement runs Case Sensitive
so if I have a record, for example
- id -nama_pelanggan
- 1 - Wira
I wil get the result if I call it this way : select
penggunaselect('mycursor', 'Wi');
but I don't got any row if I call it like this : select
penggunaselect('mycursor', 'Wi');
I need the search to run in not-case-sensitive mode
so please if you have any suggestion
Thank you
Anyway, this is the code
CREATE OR REPLACE FUNCTION penggunaselect(curs refcursor, pnama
character varying, pgrup character varying, paktif integer)
RETURNS refcursor AS
$BODY$
declare
pid_grup integer := 0;
sqltext character varying = '';
begin
-- cari id grup
if pgrup '' then
select id into pid_grup from grup where nama_grup = pgrup;
end if;
sqltext = 'select p.nama_pengguna, g.nama_grup, p.login_terakhir,
p.mulai_dibuat, p.aktif, p.id
from pengguna p, grup g
where p.id_grup = g.id';
if pnama '' then
sqltext = sqltext || ' and p.nama_pengguna like ''%' || pnama
|| '%'' ';
end if;
if pid_grup 0 then
sqltext = sqltext || ' and p.id_grup = ' || pid_grup;
end if;
if paktif = 1 then
sqltext = sqltext || ' and p.aktif = true';
end if;
if paktif = 2 then
sqltext = sqltext || ' and p.aktif = false';
end if;
sqltext = sqltext || ' order by p.nama_pengguna';
open curs for execute(sqltext);
return curs;
end;
$BODY$
LANGUAGE 'plpgsql';
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] is there any error for my postgresql installation?
I have just installed postgresql 8.3 on my debian sid, and have set the password of both system user postgres and database user to the same password, but when i connect to it using pgadmin3(from the local machine), i got the error: -- An error has occurred: Error connecting to the server: FATAL: password authentication failed for user postgres how can i fix it so that i can get pgadmin work? here is my pg_hda.conf # Database administrative login by UNIX sockets local all postgres ident sameuser # TYPE DATABASEUSERCIDR-ADDRESS METHOD # local is for Unix domain socket connections only local all all ident sameuser # IPv4 local connections: hostall all 127.0.0.1/32 md5 # IPv6 local connections: hostall all ::1/128 md5 --- here is the log messages --- 2008-12-06 12:33:08 HKT LOG: could not load root certificate file root.crt: no SSL error reported (1) 2008-12-06 12:33:08 HKT DETAIL: Will not verify client certificates. (2) 2008-12-06 12:33:08 HKT LOG: could not create IPv6 socket: Address family not supported by protocol (3) 2008-12-06 12:33:09 HKT LOG: database system was shut down at 2008-12-06 12:32:18 HKT 2008-12-06 12:33:09 HKT LOG: autovacuum launcher started 2008-12-06 12:33:09 HKT LOG: database system is ready to accept connections 2008-12-06 12:33:09 HKT LOG: incomplete startup packet (4) -- is (1) a error? what should i do? what does (2) mean? is it normal? is (3) ok? is (4) a error? what should i do? thanks -- I'm a web developer using debian+mono(C#)+postgresql+xhtml+js+xsl+xml+css -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
