[GENERAL] triggers, transactions and locks

Dear All, Is there a way to use locks within a trigger? My example below gives the error: ERROR: unexpected error -8 in EXECUTE of query BEGIN CONTEXT: PL/pgSQL function insert_into_t1 line 6 at execute statement Thanks Colin CREATE FUNCTION insert_into_t1() RETURNS trigger AS' DECLARE

Re: [GENERAL] triggers, transactions and locks

Is there a way to use locks within a trigger? My example below gives the error: It's not the lock that's the problem I think. The begin work is failing because you can't start a transaction inside the function. Were you trying to release the lock at the commit in the function? Yes I was using

[GENERAL] adding missing FROM-clause

Dear All, I have a simple join query SELECT c1 FROM t1 INNER JOIN t2 ON t2.c2 = t1.c2 WHERE t3.c3= t2.c3; Which gives the expected result but I get the message NOTICE: adding missing FROM-clause entry for table t3 How do I get rid of this NOTICE, i.e. how should I construct my select query.

[GENERAL] Storing jpgs

Dear All, What's the best way to store jpgs in postgresql to use in a web page? I tried to use large objects, but how would you extract them from a table to be viewed in a web-page without having to write them to a scratch file somewhere first? Thanks Colin

[GENERAL] INSERTS and Queries

Dear All, I can use insert with 'select' if I do this INSERT INTO TABLE t1 (col1) SELECT stuff FROM t2; But I want to insert more than one thing into the table, e.g. INSERT INTO TABLE t1 (col1,col2) SELECT stuff FROM t2 , 100; Any ideas on how I would achieve this? Thanks Colin

[GENERAL] autodoc and debian

Dear All, I have been using postgresql-autodoc with great results, however, recently I have been getting a segmentation fault when using it. I contacted the author of autodoc, who said that it appears to be a debian problem (other debian users have experienced the same problems). However,

[GENERAL] Inputting columns of data

Dear All, I have a text file with data like: 1 2 3 4 5 6 7 8 9 i.e. so I have three columns of numbers. I wish to put this data into a table. However, I do not want it to take up three rows, rather only a single row and a single column. I was think something like this CREATE TABLE t1(data

[GENERAL] pl/pythonu

Dear All, Could anyone explain why this function does will not work? The error message is DETAIL: exceptions.RuntimeError: maximum recursion depth exceeded. CREATE FUNCTION testing() RETURNS trigger AS' plan=plpy.prepare(''INSERT INTO t1 values ($1)'',[''text'']) plpy.execute(plan,[''blah''])

[GENERAL] Getting table attributes

Dear All, I'm trying to get a table containing all the user created tables, which contains the column name and their types. Basically, I want to be able to do \d all_user_tables - but I can't use the \d notation. I tried to combine the pg_ tables, but I couldn't get what I wanted (due to my

[GENERAL] Copy and xml files

Dear All, I'm trying to insert an xml file into my database. I have a table with a single text column. My intention is just to have the xml file take up one row in the table. I've tried the command COPY t1 FROM '/tmp/file.xml'; but keep getting the error message ERROR: extra data after

[GENERAL] Simulation output using libpq

Dear All, I have some c code that writes results directly to a file as they're produced during a simulation. I want to modify the code to write directly to a postgres database using libpq. I think I could open a connection and insert into the database with a transaction, commiting at the end of

Re: [GENERAL] Changing user

tried many variations on this theme but have no luck. Suggestions? Thanks Colin C G [EMAIL PROTECTED] writes: SET SESSION AUTHORIZATION username; ERROR: syntax error at or near $1 at character 28 You'll need to use EXECUTE to do this. Utility statements in general aren't prepared to deal

[GENERAL] Changing user

Dear All, I have a simple table CREATE TABLE table1{ user name, si numeric }; Also I have function: CREATE FUNCTION check1(numeric) RETURN text AS' DECLARE SI ALIAS AS $1; username name; BEGIN username:=(SELECT user FROM table1 WHERE si=SI) SET SESSION AUTHORIZATION username; RETURN username;

[GENERAL] Users and session ids

Dear All, I wonder if anyone can advise me with this problem. 1. A user logs into the database (through web, webservice, some other piece of software) - connect(user=joe,passwd=blogs) 2. We generate a random session key which will expire in 1 hour. Put this in table (user, SessKey, time). 3.

[GENERAL] Changing xml in a database

Dear All, I have installed the contrib xml package and have it working, but I would like some advice on the best way to use it. In particular, how should I alter xml files? For instance, if I have: nameNone/name and I wanted to update 'None' to 'John', what's the best way of doing this? Can