[SQL] Copying data with triggers
Hi all, In the database I'm designing, I want to have audit tables that keep a log of all inserts, updates and deletes that occur on any table. e.g. If i had a table Info, create table Info ( info_id SERIAL, some_data text ) I would also have a corresponding audit table create table AudInfo (aud_key_id SERIAL, info_id int4, some_data text, aud_operation_type, aud_log_time timestamp default now() ) now I tried creating a trigger on Info, so that whenever an insert occurs, the records are copied to the audit table. create function tp_info () returns opaque as ' begin -- insert into audit table insert into AudInfo (info_id, some_data, aud_operation_type) values (new.info_id, new.some_data, ''i''); return new; end; ' language 'plpgsql'; create trigger tp_info before insert on Info for each row execute procedure tp_info(); This doesn't work however. A parse error occurs in the first line. I suspect that I cannot insert a record into another table in a trigger. I'm not sure why though. Anybody else done similar operations within a trigger procedure? Or know of a work around? Cheers, Keith.
[SQL] tip: weird parse error for pl/pgsql
Hi everyone, After fiddling for about a day to work out why my pl/pgsql stored procedures weren't working I finally discovered why. It seems that pl/pgsql has a problem parsing Window style new line characters. I was writing my stored procedures using a Windows app, and then running them on my Linux Postgres database. It keeped on giving me... an error found one line 1 near " " which wasn't very helpful. Anyway just make sure you write your stored procs in your Unix environment or save them as Unix format. I hope this tip saves somebody some time. :) Cheers, Keith.
Re: [SQL] Re: Argument variables for select
Hi Andreas, I've worked with MS SQL stored procedures before and they are quite powerful. Its a shame postgres doesn't have the same level of features as offered by MS SQL, but apart from this area it is still a very good database. Perhaps in the coming versions we will see more stored procedure features? I'm not sure what your application is exactly but there are ways to work around the features that are missing. Good luck with convincing your boss :) Keith. At 02:28 PM 29/08/2000 +0200, Andreas Tille wrote: >On Mon, 28 Aug 2000, Yury Don wrote: > > > > Create Function VarSelect ( varchar, varchar ) > > >returns int > > >As ' > > > Declare num int ; > > > > > > Begin > > >Select Into num Count(*) From $1 Where $2 ; > > >return num; > > > End ; > > >' language 'plpgsql' ; > > > > > > Could someone please explain who to type the exact syntax so that > > > I can ship the table to select from as $1 and the condition to select > > > what as $2? Or do I have to concatenate a string with the whole > > > select statement and how to call this string? In MS SQL server this > > > could be done with > > >Exec ( query ) > > > > > AFAIK it's impossible with plpgsql, but it's possible in pltcl. > >It is really hard to believe that I'm in so very deep trouble with >PostgreSQL. It seems to me that stored procedures are far from beeing >as usefull as I learned them to know in MS SQL server. Once I try >to follow one hint I'm standing in frot of the next even harder problem. > > >web=# create function testfunc( ) >web-# returns int >web-# As ' >web'# spi_exec "SELECT count(*) AS $num FROM testtable" >web'# >web'# return $num ; >web'# End; ' >web-# language 'pltcl' ; >ERROR: Unrecognized language specified in a CREATE FUNCTION: >'pltcl'. Recognized languages are sql, C, internal and the created >procedural languages. > > >I have installed the pgtcl package of my Debian distribution, so I guess >it should be available. Once more the question: Is it really necessary to >use a further interpreter instead of sticking with SQL commands to use >the original problem. > >I have to admit that my boss wonders why I'm switching from a working >solution (MS SQL) to so much trouble :-(. I really hope to convince him >to OpenSource but it's much harder than I expected. > >Kind regards > > Andreas.
[SQL] installing pgaccess
Anybody know how to compile pgaccess from postgres source files? It must be a configure option, but I can't find it. Keith.
Re: [SQL] how to store a query, that results in a table
This is not really possible with postgresql at the moment. Better off trying to work around, perhaps using a view. That way you have a way to change the select statement without actually modifying your client code. Keith. At 06:09 PM 22/09/2000 -0400, Nelson wrote: >thank you jie Liang for your response, but my problems are: >1. How to store a query in the database. >2. How to give a parameter from outside of database, for example: >select * from table1 where row1 = my_parameter_outside. >Give me an example please. > >
[SQL]
Hi ppl, Just wondering if anyone knows if its possible to set the transaction isolation level from inside a stored procedure in plpgsql. Or do I need to run the 'set transaction isolation level' command before I called the stored procedure? I'm not even sure that would work, I assume plpgsql uses the current transaction isolation level? Any help would be much appreciated :) Cheers, Keith.
[SQL] Setting the transaction isolation level for a stored procedure
Sorry, I forgot to put a subject title in my last email. So here it is again. Hi ppl, Just wondering if anyone knows if its possible to set the transaction isolation level from inside a stored procedure in plpgsql. Or do I need to run the 'set transaction isolation level' command before I called the stored procedure? I'm not even sure that would work, I assume plpgsql uses the current transaction isolation level? Any help would be much appreciated :) Cheers, Keith.
[SQL] Transaction isolation level for plpgsql
Hi all, Does anybody know the transaction isolation level default when a plpgsql stored procedure is called? Is it possible to set it? Or is this controlled by SPI? Cheers, Keith.
Re: [SQL] trigger examples
This is an example script I use to keep a log of all database operations on my tables. Hope it helps. At 05:05 PM 3/10/2000 +1000, Carolyn Lu Wong wrote: >[EMAIL PROTECTED] wrote: > > > > Carolyn Lu Wong wrote: > > > > > > I need to write a trigger to create a new record in log table if it's a > > > new record or if column A and B changes. Can someone give me an example > > > how to do this. > > > > > > Are there any sites that gives trigger examples?? > > > > > > Thanks. > > > > the fulltextindex in the contrib dir gives an example > >The example uses C. Is it possible to create the trigger with plpsql >function? table_customer.sql
Re: [SQL] Persistent Connects (pg_pconnect)
Hi Colleen,
When you use specify a host parameter... Php will attempt to connect to
your postgres server using TCP/IP...
if you are not running the postgres backend with the -i option, then these
connections won't happen. When you don't
specify a host name then Php will use a local Unix socket.
Not sure about the persistant database connection stuff. What version of
Php are you using?
The only thing I could say, is to try explicitly closing the persistent
database connections in your Php code.
Maybe there is a bug in Php.
Hope this helps,
Keith.
At 12:34 PM 21/11/2000 +, Colleen Williams wrote:
>Hello all,
>
>I am having some problems with persistent connections.
>
>The PHP scripts in my content management system (CMS) are connecting using
>pg_pconnect(" dbname='MYDB' user='user' password='password''
>port='5432' "). I have not been able to connect to the PostgreSQL
>database when I include the host parameter in the pg_pconnect string and I
>don't understand why. Each time one of us uses the CMS, a new persistent
>connection process owned by postgres is started up. With 3 people
>accessing the CMS sporadically, 32 persistent connections are in existence
>and then Linux tells me there are too many connections! Sometimes I
>believe existing connections are reused but not often!
>
>Has anyone else had this sort of experience with persistent connections?
>It is driving me mad so I would appreciate any help or pointers you might have.
>
>Thanks,
>Colleen.
>
>Colleen Williams
>[EMAIL PROTECTED]
>
>0207 484 8825
>
>Digital Arts | British Columbia House | 1 Regent Street | London | SW1Y 4NR
>http://www.digital-arts.co.uk
Re: [SQL] Persistent Connects (pg_pconnect)
Just looked at the Php documentation... according to that.. it goes...
"An 'identical' connection is a connection that was opened to the same
host, with the same username and the same password (where applicable)."
Perhaps that means you need to specify the host for this to work.
Let me know on your findings. I'll be interested to know.
Cheers,
Keith :)
At 12:34 PM 21/11/2000 +, Colleen Williams wrote:
>Hello all,
>
>I am having some problems with persistent connections.
>
>The PHP scripts in my content management system (CMS) are connecting using
>pg_pconnect(" dbname='MYDB' user='user' password='password''
>port='5432' "). I have not been able to connect to the PostgreSQL
>database when I include the host parameter in the pg_pconnect string and I
>don't understand why. Each time one of us uses the CMS, a new persistent
>connection process owned by postgres is started up. With 3 people
>accessing the CMS sporadically, 32 persistent connections are in existence
>and then Linux tells me there are too many connections! Sometimes I
>believe existing connections are reused but not often!
>
>Has anyone else had this sort of experience with persistent connections?
>It is driving me mad so I would appreciate any help or pointers you might have.
>
>Thanks,
>Colleen.
>
>Colleen Williams
>[EMAIL PROTECTED]
>
>0207 484 8825
>
>Digital Arts | British Columbia House | 1 Regent Street | London | SW1Y 4NR
>http://www.digital-arts.co.uk
Re: [SQL] Blob Upload
Do you mean VARCHAR[30] or VARCHAR(30)?? I think you're creating an array of chars... but what you want is just a VARCHAR type with 30 characters... It may explain the array error you are getting. Keith At 11:07 AM 21/11/2000 -0200, Aristeu Gil Alves Junior wrote: >I´m trying to upload a gif image from a web interface (apache/php) without >success. >I´m using the input type ="file". >Have someone already made this work? > >And even when I try to upload from the SQL interface of Tkl PgAccess... : >The table: >CREATE TABLEimages (name VARCHAR[30] , img OID); > >SQL Command >INSERT INTO images (name, img) VALUES (´img01.gif´ , >lo_import(´/images/test.gif´) ); > >it brings up the error: >ERROR: array_in: Need to specify dimension > >Any help will be of great value. >Thanks to all. > >Best Regards > >Aristeu Gil Alves Junior<[EMAIL PROTECTED]> >IT Analyst >Porto Alegre/RS - Brasil > >"Communications without intelligence is noise; >Intelligence without communications is irrelevant." >Gen. Alfred. M. Gray, USMC
Re: [SQL] Create table doesn't work in plpgsql
I believe a couple of months back... a EXECUTE command was added to plpgsql to allow users to dynamic sql statements. So if you get the current development version you should be able to call EXECUTE CREATE TABLE or whatever sql you like. (I think this is still not in 7.03, not sure though) I'm not sure about the exact syntax, but if you look in past threads for "execute" I'm sure you'll find it. Have fun. Merry Xmas. Keith. At 02:00 PM 22/12/2000 +0100, Volker Paul wrote: > > Can this be done using tcl or perl? > >I'll try them and report what I find out. > >V.Paul
Re: [SQL] maybe Offtopic : PostgreSQL & PHP ?
Not quite sure how your code is organised...
but you could access the variable $conn by including "connect.php" into the
"query.php" script.
Otherwise, you will need to use persistent connections... which can be
achieved via pg_pconnect...
a persistent connection will instead of creating a new database connection
each time.. it will try to use
an existing connection that is no longer being used (persistent connections
do tend to have a lot of quirks tho)
Keith
At 11:33 AM 18/04/2001 +0200, Picard, Cyril wrote:
>Hi all, sorry for the maybe offtopic questions, but I did not get any answer
>from the PHP ML.
>
>I would like to query a postgresql db with the php language.
>Today I wrote a script (connectandquery.php) performing the following :
>- connect to the DB : $conn = pg_Connect("dbname = foo");
>- execute the query : $res = pg_Exec($conn,"SELECT * from BAR");
>
>
>But I would like to write this in two scripts :
>- connect.php : $conn = pg_Connect("dbname = foo");
>- query.php : $res = pg_Exec($conn,"SELECT * from BAR");
>
>but I don't know how to get the $conn variable (defined in connect.php) in
>the script query.php.
>
>Any idea is welcome !
>
>---(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 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] returning a recordset with pl/pgsql
Hi everyone, I'm using postgresql with php4. I wanted to write a stored procedure to return a recordset (using pl/pgsql). From what I've read from the documentation it seems like you can only returned postgres types like strings, booleans, etc. Is it possible to return recordsets using pl/pgsql? If so, how do I declare the return type in the declaration? Thanks in advance. Cheers, Keith.
[SQL] 8K Limit, whats the best strategy?
Hi everyone, Just wondering what strategies people have used to get around the 8K row limit in Postgres. If anyone has been troubled by this limitation before and has a nice solution around it, I would love to hear it. My application is a web-based system in which needs to store large amounts of text. The text stored needs to searchable as well. Cheers, Keith.
