[GENERAL] Trouble with PQnotifies()

Dear gurus, CREATE OR REPLACE RULE r1i AS ON INSERT TO t1 DO NOTIFY NotifyMe; CREATE OR REPLACE RULE r1u AS ON UPDATE TO t1 DO NOTIFY NotifyMe; CREATE OR REPLACE RULE r1d AS ON DELETE TO t1 DO NOTIFY NotifyMe; The main thread in my Windows application launches several worker threads. Several wor

[GENERAL] How to keep the last row of a data set?

I am trying to implement a mechanism that prohibits the last row of a data set from being deleted. CREATE TABLE t1 (c1 INTEGER,c2 INTEGER, PRIMARY KEY (c1,c2)); INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(2,1),(2,2),(2,3); My desired effect: Case 1, Permit this SQL to be executed: DELETE FROM t1

Re: [GENERAL] How to keep the last row of a data set?

> -Original Message- > From: Thomas Markus > Sent: Thu, Dec 13 2012 23:14:21 CST > To: seil...@so-net.net.tw > Subject: Re: [GENERAL] How to keep the last row of a data set? > > Hi, > > create an after delete trigger with > > IF (SELECT 1 FROM t1 limit 1) is null THEN > RAISE EXCE

Re: [GENERAL] Trouble with PQnotifies()

> The following listening worker thread behaves as expected if I insert/delete > rows into/from table "t1" in psql prompt. > > My trouble is when the SQL execution worker thread inserts/ deletes rows > into/from table "t1", the listening worker thread then goes crazy: > PQnotifies() always retu

[GENERAL] Getting reference key elements in right orders

Hi! I need to know the match columns of referencing and referenced keys. CREATE TABLE referenced (c1 TEXT,c2 TEXT, PRIMARY KEY(c1,c2)); CREATE TABLE referencing (c1 TEXT,c2 TEXT,c3 TEXT, PRIMARY KEY(c1,c2,c3), CONSTRAINT fk FOREIGN KEY (c1,c2) REFERENCES referenced (c1,c2)); The following SQL

[GENERAL] Time zone 'GMT+8'

The target pgsql is compiled from 8.4rc1. 'GMT+8' can be found in installed binary file .../share/postgresql/timezone/Etc/GMT+8. This is the recorded script: -BEGIN record-- db1=# select now(); now --- 2009-11-29 14:44:37.32241

[GENERAL] Inserting exported bytea value

I am trying to build SQL commands that insert values exported from a bytea column. Output facilities do not escape single quotes for bytea column. As such, the built INSERT SQL can be invalid because single quotes can appear in values. For example, the built (invalid) INSERT SQL command can loo

[GENERAL] Can not match 0 on bytea

Hi! Data type of table1.c1 is bytea. That column stores binary data. The following matchings do not work. What is the right syntax? TIA CN --- select c1 ~ E'\000' from table1; select c1 LIKE E'%\000%' from table1; ERROR: invalid byte sequence for encoding "UTF8": 0x00 HINT: This e

[GENERAL] regexp_replace: LF, CR, or tab

Hi! I am trying to replace characters '\r', '\n', or '\t' with space character ' '. As an example, I want string "A\t\n\rB" becomes "AB". The following statement seems to be not working. What mistake have I made? TIA CN select regexp_replace(E'A\r\n\tB',E'[\r\n\t]',' '); regexp_rep

[GENERAL] Libpq: copy file to bytea column

Hi! The data types of tableout.c1 and tablein.c1 are both bytea. I first export tableout.c1 to a file: db1=# COPY (SELECT c1 FROM tableout LIMIT 1) TO '/tmp/t'; Then I try to import the file to another table. This works without flaw: db1=# COPY tablein FROM '/tmp/t'; However, I get the follo

Re: [GENERAL] Libpq: copy file to bytea column

> > The data types of tableout.c1 and tablein.c1 are both bytea. > > I first export tableout.c1 to a file: > > > > db1=# COPY (SELECT c1 FROM tableout LIMIT 1) TO '/tmp/t'; > > > > Then I try to import the file to another table. > > > > This works without flaw: > > > > db1=# COPY tablein FROM

Re: [GENERAL] Libpq: copy file to bytea column

> What I can't understand is why PQputCopyData() encodes incoming character > string, which was exported by "COPY ... TO '/tmp/t'" SQL command under psql > prompt, for bytea columns and refuse to accept or misinterpret zero value. As far as PQputCopyData() is concerned, server and/or client does

[GENERAL] unnest array of row type

Hi! CREATE TYPE my_row_type AS (c1 SMALLINT,c2 TEXT); CREATE FUNCTION tmp_get_c1(my_row_type) RETURNS SMALLINT AS 'SELECT $1.c1' LANGUAGE SQL; CREATE FUNCTION tmp_get_c2(my_row_type) RETURNS TEXT AS 'SELECT $1.c2' LANGUAGE SQL; CREATE TABLE test (c1 SMALLINT,c2 TEXT,c3 TEXT); INSERT INTO tes

Re: [GENERAL] unnest array of row type

What I really intend to do is slightly more complicate than the original code. I need to iterate RECORD variable in PL/pgSQL. By combining both ideas from Pavel and Merlin, I get the following working function. CREATE FUNCTION test() RETURNS VOID AS $$ DECLARE rec RECORD; BEGIN C

[GENERAL] Reserve one row for every distinct value in a column

Hi! I am trying to implement a mechanism to reserve the last row for every distinct value in column "c1". CREATE TABLE table1 (c1 INTEGER,c2 INTEGER, PRIMARY KEY (c1,c2)); CREATE OR REPLACE FUNCTION keep1() RETURNS TRIGGER AS $$ BEGIN IF (SELECT COUNT(*) FROM table1 WHERE c1=OLD.c1) = 1