Re: [GENERAL] Installing on Windows without using msi Installer
Hello Jeff Maybe this one helps: http://www.giftpilz-online.de/pg_installer_setup.zip Jeff Williams schrieb: I would like to install PostgreSQL manually as part of my applications install using InnoSetup. Is there anywhere the steps required to do this and if so where would I find them? Many thanks Jeff - Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to concatenate a boolean to a text string for an EXECUTE stmt in a stored proc
Postgres 8.1 on RHEL How do I formulate this EXECUTE statement so that cust_hold (boolean column in the table) can be set to the value in v_cust_on_hold (boolean variable in a function)? v_cust_on_hold BOOLEAN; EXECUTE 'UPDATE customer_action_ytd_' || v_tyear || ' ' || 'SET bytes_sc = bytes_sc + ' || v_bytes_sc || ', ' || 'cust_hold= ' || v_cust_on_hold || ', ' || 'cust_count = cust_count + ' || v_cust_count || ' ' || 'WHERE id = ' || v_id || ' ' || 'AND cust_type = \'' || v_cust_type || '\' '; psql:runit.sql:1: ERROR: operator does not exist: text || boolean HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. CONTEXT: SQL statement SELECT 'UPDATE customer_action_ytd_' || $1 || ' ' || 'SET bytes_sc = bytes_sc + ' || $2 || ', ' || 'cust_hold = ' || $3 || ', ' || 'cust_count = cust_count + ' || $4 || ' ' || 'WHERE id = ' || $5 || ' ' || 'AND cust_type = \'' || $6 || '\' ' PL/pgSQL function kda_test_ytd_rollup line 96 at execute statement I tried a TO_CHAR(v_cust_on_hold) but received: psql:runit.sql:1: ERROR: function to_char(boolean) does not exist Thanks, Keaton
[GENERAL] table size and storage location
Hi,
I have two questions:
1) how do I find out the size, in MB, of a particular table (called
'capture' in this case).
I tried entering the SQL query SELECT (pg_tablespace_size('capture'));
The result was the following:
ERROR: tablespace capture does not exist
2) how do I find out where the actual files for the database are stored on
my system?
Where is the default storage location?
Thanks!
Charlie
--
View this message in context:
http://www.nabble.com/table-size-and-storage-location-tp16230697p16230697.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] How to concatenate a boolean to a text string for an EXECUTE stmt in a stored proc
On Wed, Mar 19, 2008 at 02:28:33PM -0600, Keaton Adams wrote: Postgres 8.1 on RHEL How do I formulate this EXECUTE statement so that cust_hold (boolean column in the table) can be set to the value in v_cust_on_hold (boolean variable in a function)? You're looking for quote_literal(), see the documentation: http://www.postgresql.org/docs/8.3/interactive/plpgsql-statements.html Section 35.8.4. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] --enable-thread-safety bug
Craig Ringer wrote: Steve Clark wrote: I guess the real question is why we are running out of memory when this option is enabled. Since my app doesn't use threads that points to a memory leak in the ecpg library when enable thread safety is turned on. It might be worth building ecpg with debug symbols then running your app, linked to that ecpg, under Valgrind. If you are able to produce more specific information about how the leak occurs in the context of your application people here may be more able to help you. -- Craig Ringer Hi Craig, I could do that - but in my situation I am not using threads so I really don't need --enable-thread-safety turned on. The freebsd ports maintainer for postgresql decided everybody should have it whether they needed it or not. I simply deleted the option from the freebsd makefile rebuilt the port - relinked my app and no more problem. I just thought the postgresql developers would want to know there was a bug. If they don't care to investigate or trouble shoot the bug it is fine by me. I just find it is interesting that a non-threaded program causes a memory leak when used with postgres libraries that are compiled with --enable-thread-safety - doesn't seem to safe to me. Have a nice day. Steve - 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] --enable-thread-safety bug
Steve Clark [EMAIL PROTECTED] writes: I could do that - but in my situation I am not using threads so I really don't need --enable-thread-safety turned on. The freebsd ports maintainer for postgresql decided everybody should have it whether they needed it or not. I simply deleted the option from the freebsd makefile rebuilt the port - relinked my app and no more problem. I just thought the postgresql developers would want to know there was a bug. If they don't care to investigate or trouble shoot the bug it is fine by me. I don't think you grasp the situation, Steve. Having enable-thread-safety turned on is standard across a wide swath of the world, and yet nobody else has reported severe memory leaks in ecpg. So there's something very specific to what your app is doing that triggers the problem. There's little point in anyone else investigating unless you can give them a test case that reproduces the misbehavior. I can assure you we would like to fix the problem if we can find it. But with no cooperation from you, we'll just have to wait until someone else stumbles across it and can show us exactly how to make it happen. regards, tom lane - Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] scheduling stored procedure
Hi, I have a temporary table in which i store all emails of users who want to receive a newsletter. to avoid spamming and to limit the size of this table, i would like to delete all requests (records) which are older than 72 hours. i was thinking to execute each hour a stored procedure which will check the time and date (of now) if it is greater than registration request time stored in this temporary table. if it is greater, so record will be deleted. does postgresql have such timer (something like cron under unix/linux) ? thanks a lot, -- Alain Windows XP SP2 PostgreSQL 8.2.4 / MS SQL server 2005 Apache 2.2.4 PHP 5.2.4 C# 2005-2008
Re: [GENERAL] scheduling stored procedure
Traditionally, postgres lets other tools do things they're good at. like scheduling scripts to run every hour. :) I seem to recall there was a postgres-specific scheduler project somebody started, but I cannot seem to recall what it was called, much less how far along the project was. On Sun, 23 Mar 2008, Alain Roger wrote: Hi, I have a temporary table in which i store all emails of users who want to receive a newsletter. to avoid spamming and to limit the size of this table, i would like to delete all requests (records) which are older than 72 hours. i was thinking to execute each hour a stored procedure which will check the time and date (of now) if it is greater than registration request time stored in this temporary table. if it is greater, so record will be deleted. does postgresql have such timer (something like cron under unix/linux) ? thanks a lot, -- Alain Windows XP SP2 PostgreSQL 8.2.4 / MS SQL server 2005 Apache 2.2.4 PHP 5.2.4 C# 2005-2008 - 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] scheduling stored procedure
On 23/03/2008 16:08, Alain Roger wrote: i was thinking to execute each hour a stored procedure which will check the time and date (of now) if it is greater than registration request time stored in this temporary table. if it is greater, so record will be deleted. does postgresql have such timer (something like cron under unix/linux) ? Not built in. There's pgAgent which comes with pgAdmin, which ought to do what you want, though I haven't used it myself. Alternatively, you can use cron to call psql with the -c option to execute the command you need run. Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- - 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] scheduling stored procedure
On 23/03/2008, Alain Roger [EMAIL PROTECTED] wrote: Hi, I have a temporary table in which i store all emails of users who want to receive a newsletter. to avoid spamming and to limit the size of this table, i would like to delete all requests (records) which are older than 72 hours. i was thinking to execute each hour a stored procedure which will check the time and date (of now) if it is greater than registration request time stored in this temporary table. if it is greater, so record will be deleted. does postgresql have such timer (something like cron under unix/linux) ? no, but you can try pgAgent http://www.pgadmin.org/docs/1.4/pgagent.html thanks a lot, -- Alain Windows XP SP2 PostgreSQL 8.2.4 / MS SQL server 2005 Apache 2.2.4 PHP 5.2.4 C# 2005-2008 - 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] table size and storage location
chuckee wrote:
1) how do I find out the size, in MB, of a particular table (called
'capture' in this case).
I tried entering the SQL query SELECT (pg_tablespace_size('capture'));
The result was the following:
ERROR: tablespace capture does not exist
You're looking for pg_relation_size('capture') or
pg_total_relation_size('capture'). A tablespace is a named location for
creating objects.
2) how do I find out where the actual files for the database are stored on
my system?
Where is the default storage location?
The complete story is laid out in the docs here:
http://www.postgresql.org/docs/8.3/interactive/storage-file-layout.html
HTH,
Paul
-
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] How to concatenate a boolean to a text string for an EXECUTE stmt in a stored proc
On Thu, Mar 20, 2008 at 1:58 AM, Keaton Adams [EMAIL PROTECTED] wrote:
Postgres 8.1 on RHEL
How do I formulate this EXECUTE statement so that cust_hold (boolean
column in the table) can be set to the value in v_cust_on_hold (boolean
variable in a function)?
v_cust_on_hold BOOLEAN;
EXECUTE 'UPDATE customer_action_ytd_' || v_tyear || ' ' ||
'SET bytes_sc = bytes_sc + ' || v_bytes_sc || ', ' ||
'cust_hold= ' || *v_cust_on_hold* || ', ' ||
'cust_count = cust_count + ' || v_cust_count || ' ' ||
'WHERE id = ' || v_id || ' ' ||
'AND cust_type = \'' || v_cust_type || '\' ';
psql:runit.sql:1: *ERROR: operator does not exist: text || boolean
*HINT: No operator matches the given name and argument type(s). You may
need to add explicit type casts.
CONTEXT: SQL statement SELECT 'UPDATE customer_action_ytd_' || $1 ||
' ' || 'SET bytes_sc = bytes_sc + ' || $2 || ', ' || 'cust_hold = ' || $3
|| ', ' || 'cust_count = cust_count + ' || $4 || ' ' || 'WHERE id = ' ||
$5 || ' ' || 'AND cust_type = \'' || $6 || '\' '
PL/pgSQL function kda_test_ytd_rollup line 96 at execute statement
I tried a TO_CHAR(v_cust_on_hold) but received:
psql:runit.sql:1: *ERROR: function to_char(boolean) does not exist
*
You can simply use the CAST operator like so:
declare
t text;
b boolean;
begin
b = true;
t = 'SELECT 1 where true = ' || b::text || ';';
raise notice '%', t;
end;
One more thing, I noticed that you are trying to escape single quotes
(') with backslashes (\), in the last line of the EXECUTE:
'AND cust_type = \'' || v_cust_type || '\' ';
You might want to do it like this:
'AND cust_type = ''' || v_cust_type || ''' ';
Thats the SQL standard way of escaping single quotes; just precede them
with another quote.
HTH,
Best regards,
--
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
Re: [GENERAL] --enable-thread-safety bug
On Sat, Mar 22, 2008 at 04:58:28PM -0400, Steve Clark wrote: Not exactly sure what you are asking about - descriptors and auto allocating. So I guess you don't use either feature. :-) The program processes about 80 packets a day, which can update several tables. It runs continously reading udp packets from systems at remote locations coming in over the internet. But the code for processing all thoss statements is the same, with and without threading enabled. One code that differs is allocation of sqlca, but given that this structure has a mere 215 bytes (about). Even if it was allocated 80 times it would make up for a memory loss of about 164MB. Which brings up the question how long the application runs until it segfaults. As Tom already pointed out, without more information there simply is no way for us to find out what's going on. We are more than willing to dig into it, but we need more to be able to. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL! - 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] --enable-thread-safety bug
Michael Meskes wrote: On Sat, Mar 22, 2008 at 04:58:28PM -0400, Steve Clark wrote: Not exactly sure what you are asking about - descriptors and auto allocating. So I guess you don't use either feature. :-) The program processes about 80 packets a day, which can update several tables. It runs continously reading udp packets from systems at remote locations coming in over the internet. But the code for processing all thoss statements is the same, with and without threading enabled. One code that differs is allocation of sqlca, but given that this structure has a mere 215 bytes (about). Even if it was allocated 80 times it would make up for a memory loss of about 164MB. Which brings up the question how long the application runs until it segfaults. As Tom already pointed out, without more information there simply is no way for us to find out what's going on. We are more than willing to dig into it, but we need more to be able to. Michael Ok I tryed valgrind and after a while it dies with a valgrind assertion error before providing any useful data. So I tried linking with -lc_r and it appears to have stopped the leak. Without -lc_r using top my app quickly climbed over 150mbyte in memory size - it is now staying steady at about 8mb - which is about what it ran when I compiled the ecpg lib without --enable-thread-safety enabled. Now why does this make a difference in ecpg? HTH, Steve If anyone cares below is the valgrind assertion failure: valgrind: vg_malloc2.c:1008 (vgPlain_arena_malloc): Assertion `new_sb != ((void*)0)' failed. ==4166==at 0xB802BE1F: (within /usr/local/lib/valgrind/stage2) ==4166==by 0xB802BE1E: (within /usr/local/lib/valgrind/stage2) ==4166==by 0xB802BE5D: vgPlain_core_assert_fail (in /usr/local/lib/valgrind/stage2) ==4166==by 0xB8028091: vgPlain_arena_malloc (in /usr/local/lib/valgrind/stage2) sched status: Thread 1: status = Runnable, associated_mx = 0x0, associated_cv = 0x0 ==4166==at 0x3C03894B: calloc (in /usr/local/lib/valgrind/vgpreload_memcheck.so) Note: see also the FAQ.txt in the source distribution. It contains workarounds to several common problems. If that doesn't help, please report this bug to: valgrind.kde.org In the bug report, send all the above text, the valgrind version, and what Linux distro you are using. Thanks. - Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Insert
I have two tables - p_id.association and p_id.devices If a new row in p_id.association has a value - say 2 in column mon_function and a value 5 in column monitoring_fluid I want the new value for mon_function inserted into table p_id.devices ONLY when 2 AND 5 do not appear in the same row in p_id.devices. The following gives me a return of more than one row and I can't figure out what's wrong. Any thoughts would be appreciated. Bob Declare xmon_function varchar; Begin Select mon_function into xmon_function From p_id.association Where mon_function = new.mon_function; If xmon_function = p_id.devices.device_number From p_id.association, p_id.devices Where p_id.association.monitoring_fluid = p_id.devices.fluid_id or p_id.association.monitoring_fluid = p_id.devices.pipe_id Then Return Null; Elseif xmon_function = p_id.devices.device_number or xmon_function != p_id.devices.device_number From p_id.association, p_id.devices Where p_id.association.monitoring_fluid != p_id.devices.fluid_id or p_id.association.monitoring_fluid != p_id.devices.pipe_id Then Insert into p_id.devices (device_number) Values (New.mon_function) ; Return Null; END if; End; - 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] Insert
On Sunday 23 March 2008 1:48 pm, Bob Pawley wrote: I have two tables - p_id.association and p_id.devices If a new row in p_id.association has a value - say 2 in column mon_function and a value 5 in column monitoring_fluid I want the new value for mon_function inserted into table p_id.devices ONLY when 2 AND 5 do not appear in the same row in p_id.devices. The following gives me a return of more than one row and I can't figure out what's wrong. First could you send the actual CREATE FUNCTION statement. I will assume you are using pl/pgsql. Second I am assuming this is a trigger function, so the CREATE TRIGGER statement would be useful. Any thoughts would be appreciated. Bob Declare xmon_function varchar; Begin Select mon_function into xmon_function From p_id.association Where mon_function = new.mon_function; If this is a trigger function, the above is redundant. Just use new.mon_function. If xmon_function = p_id.devices.device_number From p_id.association, p_id.devices Where p_id.association.monitoring_fluid = p_id.devices.fluid_id or p_id.association.monitoring_fluid = p_id.devices.pipe_id Then You can't do this. You would need to do something along lines of SELECT p_id.devices.device_number INTO dev_no FROM ... and then IF new.mon_function = dev_no THEN Return Null; Elseif xmon_function = p_id.devices.device_number or xmon_function != p_id.devices.device_number From p_id.association, p_id.devices Where p_id.association.monitoring_fluid != p_id.devices.fluid_id or p_id.association.monitoring_fluid != p_id.devices.pipe_id See above. Then Insert into p_id.devices (device_number) Values (New.mon_function) ; Return Null; END if; End; - Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Adrian Klaver [EMAIL PROTECTED] - 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] Insert
CREATE OR REPLACE FUNCTION monitor_install() RETURNS trigger AS $$ Declare xmon_function varchar; Begin Select mon_function into xmon_function From p_id.association Where mon_function = new.mon_function; If xmon_function = p_id.devices.device_number From p_id.association, p_id.devices Where p_id.association.monitoring_fluid = p_id.devices.fluid_id or p_id.association.monitoring_fluid = p_id.devices.pipe_id Then Return Null; Elseif xmon_function = p_id.devices.device_number or xmon_function != p_id.devices.device_number From p_id.association, p_id.devices Where p_id.association.monitoring_fluid != p_id.devices.fluid_id or p_id.association.monitoring_fluid != p_id.devices.pipe_id Then Insert into p_id.devices (device_number) Values (New.mon_function) ; Return Null; END if; End; $$ LANGUAGE plpgsql; create trigger monitorinstall after update on p_id.association for each row execute procedure monitor_install(); - Original Message - From: Adrian Klaver [EMAIL PROTECTED] To: pgsql-general@postgresql.org Cc: Bob Pawley [EMAIL PROTECTED] Sent: Sunday, March 23, 2008 2:24 PM Subject: Re: [GENERAL] Insert On Sunday 23 March 2008 1:48 pm, Bob Pawley wrote: I have two tables - p_id.association and p_id.devices If a new row in p_id.association has a value - say 2 in column mon_function and a value 5 in column monitoring_fluid I want the new value for mon_function inserted into table p_id.devices ONLY when 2 AND 5 do not appear in the same row in p_id.devices. The following gives me a return of more than one row and I can't figure out what's wrong. First could you send the actual CREATE FUNCTION statement. I will assume you are using pl/pgsql. Second I am assuming this is a trigger function, so the CREATE TRIGGER statement would be useful. Any thoughts would be appreciated. Bob Declare xmon_function varchar; Begin Select mon_function into xmon_function From p_id.association Where mon_function = new.mon_function; If this is a trigger function, the above is redundant. Just use new.mon_function. If xmon_function = p_id.devices.device_number From p_id.association, p_id.devices Where p_id.association.monitoring_fluid = p_id.devices.fluid_id or p_id.association.monitoring_fluid = p_id.devices.pipe_id Then You can't do this. You would need to do something along lines of SELECT p_id.devices.device_number INTO dev_no FROM ... and then IF new.mon_function = dev_no THEN Return Null; Elseif xmon_function = p_id.devices.device_number or xmon_function != p_id.devices.device_number From p_id.association, p_id.devices Where p_id.association.monitoring_fluid != p_id.devices.fluid_id or p_id.association.monitoring_fluid != p_id.devices.pipe_id See above. Then Insert into p_id.devices (device_number) Values (New.mon_function) ; Return Null; END if; End; - Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Adrian Klaver [EMAIL PROTECTED] - 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] INFINITE RECURSION with rules...
Hi to everyone.
I've got some problem with rules.
-- I have 2 tables
CREATE TABLE a (email varchar(20), name varchar(10), num1 smallint, num2
smallint, PRIMARY KEY (email, name));
CREATE TABLE b (id smallint PRIMARY KEY, email_a varchar(20), name_a
varchar(10), tot smallint, FOREIGN KEY (email_a, name_a) REFERENCES
a(email, name));
/*
*My goal is to calculate and insert automatically the value of tot
when I insert a row into table b.*
*/
-- Some samples
INSERT INTO a VALUES ('[EMAIL PROTECTED]
mailto:[EMAIL PROTECTED]','bill',3,5);
INSERT INTO a VALUES ('[EMAIL PROTECTED]
mailto:[EMAIL PROTECTED]','paul',4,7); mailto:[EMAIL PROTECTED]
-- Then I created a simple function
CREATE OR REPLACE FUNCTION calc(varchar(10), varchar(20)) RETURNS
smallint AS $$
DECLARE
rowww a%ROWTYPE;
BEGIN
SELECT * INTO rowww FROM a WHERE email = $1 AND name = $2;
IF FOUND
THEN RETURN rowww.num1 * rowww.num2;
ELSE
RAISE WARNING 'Error: values non found!';
END IF;
END;
$$ LANGUAGE plpgsql;
-- And this easy rule
CREATE RULE rrr_a_b AS ON INSERT TO b
DO INSTEAD
INSERT INTO b VALUES
(NEW.id,
NEW.email_a,
NEW.name_a,
(SELECT calc(NEW.email_a, NEW.name_a))
);
-- Sample for insert into b
INSERT INTO b VALUES (33,'[EMAIL PROTECTED]','bill');
Trying to insert into b (and using the new rule defined by myself, i
receive this message:
/*ERROR: infinite recursion detected in rules for relation b*/
How I could solve this problem?
Re: [GENERAL] Insert
On Sunday 23 March 2008 2:40 pm, Bob Pawley wrote: CREATE OR REPLACE FUNCTION monitor_install() RETURNS trigger AS $$ Declare xmon_function varchar; Begin Select mon_function into xmon_function From p_id.association Where mon_function = new.mon_function; If xmon_function = p_id.devices.device_number From p_id.association, p_id.devices Where p_id.association.monitoring_fluid = p_id.devices.fluid_id or p_id.association.monitoring_fluid = p_id.devices.pipe_id Then Return Null; Elseif xmon_function = p_id.devices.device_number or xmon_function != p_id.devices.device_number From p_id.association, p_id.devices Where p_id.association.monitoring_fluid != p_id.devices.fluid_id or p_id.association.monitoring_fluid != p_id.devices.pipe_id Then Insert into p_id.devices (device_number) Values (New.mon_function) ; Return Null; END if; End; $$ LANGUAGE plpgsql; create trigger monitorinstall after update on p_id.association for each row execute procedure monitor_install(); Alright I am going to need to see the schema for p_id.association and p_id.devices to sort this out. The return 'more than one row' error is most likely occurring in the IF and ELSEIF clauses. There can only be one value on each side of the comparison. - Original Message - From: Adrian Klaver [EMAIL PROTECTED] To: pgsql-general@postgresql.org Cc: Bob Pawley [EMAIL PROTECTED] Sent: Sunday, March 23, 2008 2:24 PM Subject: Re: [GENERAL] Insert On Sunday 23 March 2008 1:48 pm, Bob Pawley wrote: I have two tables - p_id.association and p_id.devices If a new row in p_id.association has a value - say 2 in column mon_function and a value 5 in column monitoring_fluid I want the new value for mon_function inserted into table p_id.devices ONLY when 2 AND 5 do not appear in the same row in p_id.devices. The following gives me a return of more than one row and I can't figure out what's wrong. First could you send the actual CREATE FUNCTION statement. I will assume you are using pl/pgsql. Second I am assuming this is a trigger function, so the CREATE TRIGGER statement would be useful. Any thoughts would be appreciated. Bob Declare xmon_function varchar; Begin Select mon_function into xmon_function From p_id.association Where mon_function = new.mon_function; If this is a trigger function, the above is redundant. Just use new.mon_function. If xmon_function = p_id.devices.device_number From p_id.association, p_id.devices Where p_id.association.monitoring_fluid = p_id.devices.fluid_id or p_id.association.monitoring_fluid = p_id.devices.pipe_id Then You can't do this. You would need to do something along lines of SELECT p_id.devices.device_number INTO dev_no FROM ... and then IF new.mon_function = dev_no THEN Return Null; Elseif xmon_function = p_id.devices.device_number or xmon_function != p_id.devices.device_number From p_id.association, p_id.devices Where p_id.association.monitoring_fluid != p_id.devices.fluid_id or p_id.association.monitoring_fluid != p_id.devices.pipe_id See above. Then Insert into p_id.devices (device_number) Values (New.mon_function) ; Return Null; END if; End; - Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Adrian Klaver [EMAIL PROTECTED] - 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 -- Adrian Klaver [EMAIL PROTECTED] - 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] Insert
Is this what you need??
Bob
CREATE TABLE p_id.association
(
monitor integer,
devices_id integer NOT NULL,
mon_function integer,
monitoring_fluid integer,
ps_open character varying(5),
ps_closed character varying(5),
CONSTRAINT association_pkey PRIMARY KEY (devices_id)
)
WITHOUT OIDS;
ALTER TABLE p_id.association OWNER TO postgres;
CREATE TABLE p_id.devices
(
p_id_id integer,
devices_id integer NOT NULL DEFAULT
nextval('devices_devices_id_seq'::regclass),
fluid_id integer,
pipe_id integer,
tag_number character varying(100),
device_number integer,
idw_deviceid integer,
sump integer,
CONSTRAINT devices_pk PRIMARY KEY (devices_id)
)
WITHOUT OIDS;
ALTER TABLE p_id.devices OWNER TO postgres;
- Original Message -
From: Adrian Klaver [EMAIL PROTECTED]
To: pgsql-general@postgresql.org
Cc: Bob Pawley [EMAIL PROTECTED]
Sent: Sunday, March 23, 2008 2:58 PM
Subject: Re: [GENERAL] Insert
On Sunday 23 March 2008 2:40 pm, Bob Pawley wrote:
CREATE OR REPLACE FUNCTION monitor_install() RETURNS trigger AS $$
Declare
xmon_function varchar;
Begin
Select mon_function into xmon_function
From p_id.association
Where mon_function = new.mon_function;
If xmon_function = p_id.devices.device_number
From p_id.association, p_id.devices
Where p_id.association.monitoring_fluid = p_id.devices.fluid_id
or p_id.association.monitoring_fluid = p_id.devices.pipe_id
Then
Return Null;
Elseif xmon_function = p_id.devices.device_number
or xmon_function != p_id.devices.device_number
From p_id.association, p_id.devices
Where p_id.association.monitoring_fluid != p_id.devices.fluid_id
or p_id.association.monitoring_fluid != p_id.devices.pipe_id
Then
Insert into p_id.devices (device_number) Values (New.mon_function) ;
Return Null;
END if;
End;
$$ LANGUAGE plpgsql;
create trigger monitorinstall after update on p_id.association
for each row execute procedure monitor_install();
Alright I am going to need to see the schema for p_id.association and
p_id.devices to sort this out. The return 'more than one row' error is
most
likely occurring in the IF and ELSEIF clauses. There can only be one value
on
each side of the comparison.
- Original Message -
From: Adrian Klaver [EMAIL PROTECTED]
To: pgsql-general@postgresql.org
Cc: Bob Pawley [EMAIL PROTECTED]
Sent: Sunday, March 23, 2008 2:24 PM
Subject: Re: [GENERAL] Insert
On Sunday 23 March 2008 1:48 pm, Bob Pawley wrote:
I have two tables - p_id.association and p_id.devices
If a new row in p_id.association has a value - say 2 in column
mon_function
and a value 5 in column monitoring_fluid I want the new value for
mon_function inserted into table p_id.devices ONLY when 2 AND 5 do not
appear in the same row in p_id.devices.
The following gives me a return of more than one row and I can't
figure out what's wrong.
First could you send the actual CREATE FUNCTION statement. I will
assume
you
are using pl/pgsql.
Second I am assuming this is a trigger function, so the CREATE TRIGGER
statement would be useful.
Any thoughts would be appreciated.
Bob
Declare
xmon_function varchar;
Begin
Select mon_function into xmon_function
From p_id.association
Where mon_function = new.mon_function;
If this is a trigger function, the above is redundant. Just use
new.mon_function.
If xmon_function = p_id.devices.device_number
From p_id.association, p_id.devices
Where p_id.association.monitoring_fluid = p_id.devices.fluid_id
or p_id.association.monitoring_fluid = p_id.devices.pipe_id
Then
You can't do this. You would need to do something along lines of SELECT
p_id.devices.device_number INTO dev_no FROM ... and
then IF new.mon_function = dev_no THEN
Return Null;
Elseif xmon_function = p_id.devices.device_number
or xmon_function != p_id.devices.device_number
From p_id.association, p_id.devices
Where p_id.association.monitoring_fluid != p_id.devices.fluid_id
or p_id.association.monitoring_fluid != p_id.devices.pipe_id
See above.
Then
Insert into p_id.devices (device_number) Values (New.mon_function) ;
Return Null;
END if;
End;
-
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Adrian Klaver
[EMAIL PROTECTED]
-
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
--
Adrian Klaver
[EMAIL PROTECTED]
-
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:
Re: [GENERAL] INFINITE RECURSION with rules...
On Sun, Mar 23, 2008 at 10:30 AM, srdjan [EMAIL PROTECTED] wrote:
[...]
CREATE TABLE b (id smallint PRIMARY KEY, email_a varchar(20), name_a
varchar(10), tot smallint, FOREIGN KEY (email_a, name_a) REFERENCES a(email,
name));
[...]
-- And this easy rule
CREATE RULE rrr_a_b AS ON INSERT TO b
DO INSTEAD
INSERT INTO b VALUES
(NEW.id,
NEW.email_a,
NEW.name_a,
(SELECT calc(NEW.email_a, NEW.name_a))
);
-- Sample for insert into b
INSERT INTO b VALUES (33,'[EMAIL PROTECTED]','bill');
[...]
Trying to insert into b (and using the new rule defined by myself, i receive
this message:
ERROR: infinite recursion detected in rules for relation b
when you insert into b the rule rewrites your query into an insert
into b... ah... another insert into b, the rule rewrites *again* the
query into (guess what?) another insert into b... and the rule system
will continue rewriting your query until it get something different to
an insert into b... hope i was clear...
now, why the rule? isn't enough to simply do this?
INSERT INTO b VALUES (33,'[EMAIL PROTECTED]','bill',
calc('[EMAIL PROTECTED]', 'bill'));
or maybe using a trigger before insert but you're insert should look like:
INSERT INTO b(id, email_a, name_a) VALUES (33,'[EMAIL PROTECTED]','bill');
and in the trigger fill the tot column
--
regards,
Jaime Casanova
-
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] INFINITE RECURSION with rules...
srdjan wrote: /* *My goal is to calculate and insert automatically the value of tot when I insert a row into table b.* */ Use a trigger. It's a lot simpler. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support - 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] Insert
On Sunday 23 March 2008 3:04 pm, Bob Pawley wrote:
Is this what you need??
Bob
CREATE TABLE p_id.association
(
monitor integer,
devices_id integer NOT NULL,
mon_function integer,
monitoring_fluid integer,
ps_open character varying(5),
ps_closed character varying(5),
CONSTRAINT association_pkey PRIMARY KEY (devices_id)
)
WITHOUT OIDS;
ALTER TABLE p_id.association OWNER TO postgres;
CREATE TABLE p_id.devices
(
p_id_id integer,
devices_id integer NOT NULL DEFAULT
nextval('devices_devices_id_seq'::regclass),
fluid_id integer,
pipe_id integer,
tag_number character varying(100),
device_number integer,
idw_deviceid integer,
sump integer,
CONSTRAINT devices_pk PRIMARY KEY (devices_id)
)
WITHOUT OIDS;
ALTER TABLE p_id.devices OWNER TO postgres;
Yes. Some further questions. How are association and devices related? I see
devices_id in both so is one a Foreign Key to the other? I ask because in
your function you relate them via association.mon_fluid=devices.device_number
as well as
p_id.association.monitoring_fluid = p_id.devices.fluid_id
or p_id.association.monitoring_fluid = p_id.devices.pipe_id
I am not quite sure I understand monitoring_fluid=pipe_id.
- Original Message -
From: Adrian Klaver [EMAIL PROTECTED]
To: pgsql-general@postgresql.org
Cc: Bob Pawley [EMAIL PROTECTED]
Sent: Sunday, March 23, 2008 2:58 PM
Subject: Re: [GENERAL] Insert
On Sunday 23 March 2008 2:40 pm, Bob Pawley wrote:
CREATE OR REPLACE FUNCTION monitor_install() RETURNS trigger AS $$
Declare
xmon_function varchar;
Begin
Select mon_function into xmon_function
From p_id.association
Where mon_function = new.mon_function;
If xmon_function = p_id.devices.device_number
From p_id.association, p_id.devices
Where p_id.association.monitoring_fluid = p_id.devices.fluid_id
or p_id.association.monitoring_fluid = p_id.devices.pipe_id
Then
Return Null;
Elseif xmon_function = p_id.devices.device_number
or xmon_function != p_id.devices.device_number
From p_id.association, p_id.devices
Where p_id.association.monitoring_fluid != p_id.devices.fluid_id
or p_id.association.monitoring_fluid != p_id.devices.pipe_id
Then
Insert into p_id.devices (device_number) Values (New.mon_function) ;
Return Null;
END if;
End;
$$ LANGUAGE plpgsql;
create trigger monitorinstall after update on p_id.association
for each row execute procedure monitor_install();
Alright I am going to need to see the schema for p_id.association and
p_id.devices to sort this out. The return 'more than one row' error is
most
likely occurring in the IF and ELSEIF clauses. There can only be one
value on
each side of the comparison.
- Original Message -
From: Adrian Klaver [EMAIL PROTECTED]
To: pgsql-general@postgresql.org
Cc: Bob Pawley [EMAIL PROTECTED]
Sent: Sunday, March 23, 2008 2:24 PM
Subject: Re: [GENERAL] Insert
On Sunday 23 March 2008 1:48 pm, Bob Pawley wrote:
I have two tables - p_id.association and p_id.devices
If a new row in p_id.association has a value - say 2 in column
mon_function
and a value 5 in column monitoring_fluid I want the new value for
mon_function inserted into table p_id.devices ONLY when 2 AND 5 do
not appear in the same row in p_id.devices.
The following gives me a return of more than one row and I can't
figure out what's wrong.
First could you send the actual CREATE FUNCTION statement. I will
assume
you
are using pl/pgsql.
Second I am assuming this is a trigger function, so the CREATE TRIGGER
statement would be useful.
Any thoughts would be appreciated.
Bob
Declare
xmon_function varchar;
Begin
Select mon_function into xmon_function
From p_id.association
Where mon_function = new.mon_function;
If this is a trigger function, the above is redundant. Just use
new.mon_function.
If xmon_function = p_id.devices.device_number
From p_id.association, p_id.devices
Where p_id.association.monitoring_fluid = p_id.devices.fluid_id
or p_id.association.monitoring_fluid = p_id.devices.pipe_id
Then
You can't do this. You would need to do something along lines of
SELECT p_id.devices.device_number INTO dev_no FROM ... and
then IF new.mon_function = dev_no THEN
Return Null;
Elseif xmon_function = p_id.devices.device_number
or xmon_function != p_id.devices.device_number
From p_id.association, p_id.devices
Where p_id.association.monitoring_fluid != p_id.devices.fluid_id
or p_id.association.monitoring_fluid != p_id.devices.pipe_id
See above.
Then
Insert into p_id.devices (device_number) Values (New.mon_function) ;
Return Null;
END if;
End;
-
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
Re: [GENERAL] Insert
On Sunday 23 March 2008 3:04 pm, Bob Pawley wrote:
Is this what you need??
Bob
CREATE TABLE p_id.association
(
monitor integer,
devices_id integer NOT NULL,
mon_function integer,
monitoring_fluid integer,
ps_open character varying(5),
ps_closed character varying(5),
CONSTRAINT association_pkey PRIMARY KEY (devices_id)
)
WITHOUT OIDS;
ALTER TABLE p_id.association OWNER TO postgres;
CREATE TABLE p_id.devices
(
p_id_id integer,
devices_id integer NOT NULL DEFAULT
nextval('devices_devices_id_seq'::regclass),
fluid_id integer,
pipe_id integer,
tag_number character varying(100),
device_number integer,
idw_deviceid integer,
sump integer,
CONSTRAINT devices_pk PRIMARY KEY (devices_id)
)
WITHOUT OIDS;
ALTER TABLE p_id.devices OWNER TO postgres;
Going off what I could deduce I came up with this. It needs to be proofread
and is not tested.
CREATE OR REPLACE FUNCTION monitor_install() RETURNS trigger AS $$
DECLARE
dev_ct integer;
BEGIN
SELECT count(*)INTO dev_ct FROM p_id_p_id.devices WHERE new.mon_function =
p_id.devices.device_number AND
(p_id.association.monitoring_fluid = p_id.devices.fluid_id OR
p_id.association.monitoring_fluid = p_id.devices.pipe_id)
IF dev_ct 0 THEN
RETURN NULL;
ELSE
INSERT INTO p_id.devices (device_number) VALUES (New.mon_function) ;
END IF;
END;
$$ LANGUAGE plpgsql;
create trigger monitorinstall after update on p_id.association
for each row execute procedure monitor_install();
- Original Message -
From: Adrian Klaver [EMAIL PROTECTED]
To: pgsql-general@postgresql.org
Cc: Bob Pawley [EMAIL PROTECTED]
Sent: Sunday, March 23, 2008 2:58 PM
Subject: Re: [GENERAL] Insert
On Sunday 23 March 2008 2:40 pm, Bob Pawley wrote:
CREATE OR REPLACE FUNCTION monitor_install() RETURNS trigger AS $$
Declare
xmon_function varchar;
Begin
Select mon_function into xmon_function
From p_id.association
Where mon_function = new.mon_function;
If xmon_function = p_id.devices.device_number
From p_id.association, p_id.devices
Where p_id.association.monitoring_fluid = p_id.devices.fluid_id
or p_id.association.monitoring_fluid = p_id.devices.pipe_id
Then
Return Null;
Elseif xmon_function = p_id.devices.device_number
or xmon_function != p_id.devices.device_number
From p_id.association, p_id.devices
Where p_id.association.monitoring_fluid != p_id.devices.fluid_id
or p_id.association.monitoring_fluid != p_id.devices.pipe_id
Then
Insert into p_id.devices (device_number) Values (New.mon_function) ;
Return Null;
END if;
End;
$$ LANGUAGE plpgsql;
create trigger monitorinstall after update on p_id.association
for each row execute procedure monitor_install();
Alright I am going to need to see the schema for p_id.association and
p_id.devices to sort this out. The return 'more than one row' error is
most
likely occurring in the IF and ELSEIF clauses. There can only be one
value on
each side of the comparison.
- Original Message -
From: Adrian Klaver [EMAIL PROTECTED]
To: pgsql-general@postgresql.org
Cc: Bob Pawley [EMAIL PROTECTED]
Sent: Sunday, March 23, 2008 2:24 PM
Subject: Re: [GENERAL] Insert
On Sunday 23 March 2008 1:48 pm, Bob Pawley wrote:
I have two tables - p_id.association and p_id.devices
If a new row in p_id.association has a value - say 2 in column
mon_function
and a value 5 in column monitoring_fluid I want the new value for
mon_function inserted into table p_id.devices ONLY when 2 AND 5 do
not appear in the same row in p_id.devices.
The following gives me a return of more than one row and I can't
figure out what's wrong.
First could you send the actual CREATE FUNCTION statement. I will
assume
you
are using pl/pgsql.
Second I am assuming this is a trigger function, so the CREATE TRIGGER
statement would be useful.
Any thoughts would be appreciated.
Bob
Declare
xmon_function varchar;
Begin
Select mon_function into xmon_function
From p_id.association
Where mon_function = new.mon_function;
If this is a trigger function, the above is redundant. Just use
new.mon_function.
If xmon_function = p_id.devices.device_number
From p_id.association, p_id.devices
Where p_id.association.monitoring_fluid = p_id.devices.fluid_id
or p_id.association.monitoring_fluid = p_id.devices.pipe_id
Then
You can't do this. You would need to do something along lines of
SELECT p_id.devices.device_number INTO dev_no FROM ... and
then IF new.mon_function = dev_no THEN
Return Null;
Elseif xmon_function = p_id.devices.device_number
or xmon_function != p_id.devices.device_number
From p_id.association, p_id.devices
Where p_id.association.monitoring_fluid != p_id.devices.fluid_id
or p_id.association.monitoring_fluid !=
[GENERAL] tabs in psql
Hi, I'm fairly new to postgresql, and I'm having some problems understanding the syntax of SQL statements in psql. According to the documentation, white space can be used freely. However, the following fails: CREATE TABLE test ( id Int8, phenotype Varchar(510), genotype Varchar(510) ); testdb( ERROR: syntax error at or near Varchar LINE 4: phenotype Varchar(510), ^ where the field name and data type spec are separated by a tab character. However, this works if the tab is separated by a single space. Can someone please explain how this is controlled, and what the proper syntax should be? Thanks in advance. Cheers, -- Seb - 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] tabs in psql
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Sun, 23 Mar 2008 19:35:46 -0500 Seb [EMAIL PROTECTED] wrote: CREATE TABLE test ( idInt8, phenotype Varchar(510), genotype Varchar(510) ); serial_test=# CREATE TABLE test ( id Int8, phenotype Varchar(510), genotype Varchar(510) ); CREATE TABLE I can not duplicate your problem. However when I copy and paste directly from your email it does indeed fail watch: serial_test=# CREATE TABLE test serial_test-# ( serial_test(# idInt8, serial_test(# phenotype Varchar(510), serial_test(# genotype Varchar(510) serial_test(# ); ERROR: syntax error at or near Varchar LINE 4: phenotype Varchar(510), ^ That is a direct copy and paste from your email. Notice that there is no space between id and Int8. Once I manually put the space in, it worked as expected. Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFH5vpJATb/zqfZUUQRAk+zAJ0WerkdEjY2eDB24OBQMRn4iyHCngCfahBs r8zG0uQChcr11XLkZ5dtY5A= =k3m8 -END PGP SIGNATURE- - 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] tabs in psql
On Sun, 23 Mar 2008 17:48:09 -0700, Joshua D. Drake [EMAIL PROTECTED] wrote: [...] I can not duplicate your problem. However when I copy and paste directly from your email it does indeed fail watch: [...] That is a direct copy and paste from your email. Notice that there is no space between id and Int8. Once I manually put the space in, it worked as expected. Thanks Joshua, that is the problem I wanted to illustrate; i.e. whenever a tab separates the field name from the data type spec, the tab character gets swallowed and psql cannot parse correctly. So it seems tabs are not interpreted as simple white space, and it should not be used in this case, am I right? -- Seb - 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] tabs in psql
On Sun, 23 Mar 2008 20:01:28 -0500 Seb [EMAIL PROTECTED] wrote: Thanks Joshua, that is the problem I wanted to illustrate; i.e. whenever a tab separates the field name from the data type spec, the tab character gets swallowed and psql cannot parse correctly. So it seems tabs are not interpreted as simple white space, and it should not be used in this case, am I right? Actually that would make sense as psql supports tab complete so if the terminal interprets a tab, it isn't going to treat it as spaces. I would suggest changing your editor to treat tabs as 4 spaces. Sincerely, Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit signature.asc Description: PGP signature
Re: [GENERAL] tabs in psql
On Sun, 23 Mar 2008 18:15:55 -0700, Joshua D. Drake [EMAIL PROTECTED] wrote: [...] Actually that would make sense as psql supports tab complete so if the terminal interprets a tab, it isn't going to treat it as spaces. I would suggest changing your editor to treat tabs as 4 spaces. But the problem still occurs with 'psql -f INPUTFILE', where tab complete is irrelevant, so I'm confused. -- Seb - 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] tabs in psql
Seb [EMAIL PROTECTED] writes: But the problem still occurs with 'psql -f INPUTFILE', where tab complete is irrelevant, so I'm confused. You sure? I can't make the example fail that way. It does fail when the example-with-tab is cut and pasted directly into a terminal window, which is exactly as expected because that's exactly what would happen if you interactively typed a TAB at that point. psql doesn't see anything to complete with so it just beeps at you, without adding anything to the input. If you need to enter stuff like this interactively in psql, use -n to disable readline. You really shouldn't need -n to prevent tab completion in a -f file though. If you can reproduce that then file a bug against whatever version of readline you're using. regards, tom lane - Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
