[SQL] using SQL to evaluate arbitrary expressions?

Hi, we need to process user-defined integrity conditions, which are stored as strings at application level, e. g. "person.age - 2 > 18". We would like to substitute the variable names within these expressions by their values at trigger time. So we have expressions containing constants only, e.

[SQL] getting the oid for a new tuple in a BEFORE trigger

Hi, we need to control database changes within BEFORE triggers. There is no problem with triggers called by update, but there is a problem with triggers called by insert. We strongly need to know the oid of a newly inserted tuple. In this case, we use tg_newtuple of the TriggerData structure pa

[SQL] SPI_exec does not return!

Hi, if I do a SELECT on a non-existing table, SPI_exec won't return. I get, e. g. in pgaccess, an error message " does not exist", and my code below the function call is never reached. So why return error codes for SPI_exec when you cannot evaluate them? The background: I need to distinguish d

[SQL] interpreting attributes in pg_class

Hi, I need to find the entries for "my" tables within pg_class, without the indexes ("..._pkey"). How does one have to interpret "reltype" and that, and where are the docs/header files? Thanks, Markus ---(end of broadcast)--- TIP 1: subscribe and

Re: [SQL] Re: Help!!! Trying to "SELECT" and get a tree structure back.

Hi Joe, what if you want to insert a node somewhere in the tree? You have to update all nodes, right? Markus On Friday 29 June 2001 21:06, --CELKO-- wrote: > > Albert (1,12) > /\ > /\ > Bert (2,3)Chuck (4,11) >/

[SQL] traversing foreign key relationships between tables

Hi, I have the following problem. There is a trigger event on a table T. Table t is linked through n intermediate tables (mostly n = 0) to a primary master Table M. What I need to do is find the entry in M which corresponds to the triggered entry in T. I think in pseudocode it would be like th

[SQL] loosing connection after function call

normally before or while processing the request. psql:sql/test.sql:19: connection to server was lost make: *** [dbs] Error 2 Please help! Thank you, Markus Wagner ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html

[SQL] distinguishing different database connections

Hi, can I access information on the current connection from within a trigger function? I need to identify different server connections somehow. Something like a "connection id" would be enough. The background: When a user starts a frontend application (NT, Access) he starts a new connection to

[SQL] evaluating values of attributes from computed attribute names in trigger function

Hi, I need to find all changed fields in a trigger function. I found out how to get the names of all attributes within plpgsql, but I cannot get the value of NEW and OLD for the attribute with that name. That is: assume there is NEW.foo and OLD.foo let the name 'foo' be computed and stored in

[SQL] unrecognized language 'plpgsql'

Hi, when creating a function I get the error message 'unrecognized language'. What's this? I need a powerful language which can retrieve many things (username, tables, attributes, etc.). Isn't plpgsql the best choice? Why this message and where can I find more documentation on plpgsql? Thank you

[SQL] audit trail and system catalogs

Hi, we strongly need to implement an audit trail as a prerequisite for clinical trials, that is a functionality which records any change of any data item in a database into one single table containing these events. I found that one could use rules for this, but this would require one rule for

[SQL] hiding pg messages in c programs

Hi, how can I disable the output of pg sql commands on the shell from within my C programs? Thanks, Markus ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED]

[SQL] audit trail and system catalogs

Hi, we strongly need to implement an audit trail as a prerequisite for clinical trials, that is a functionality which records any change of any data item in a database into one single table containing these events. I found that one could use rules for this, but this would require one rule for

[SQL] postmaster admin questions

Hi, we are using several Windows-clients to access a pg database on a linux machine. When we want to recreate/change the database "dropdb" says that there still are users connected to it. How can I see which users are connected and how can I disconnect them/ get my db dropped anyway?? Thank yo

[SQL] handling special characters in sql strings

Hi, I need to insert and retrieve strings containing special characters (e. g. "'") and I want a C function to prepare my strings automatically. Is it ok to just insert a "\" before each special character? And what range of characters will need this handling? Thanks, Markus ---

[SQL] platform independend db access?

Hi, I would like my C source code to compile under unix and windows. I am currently using the pg libs to access my database. Under Windows I'd like to use odbc. What's the most platform (and db) independend way to access the database? Since odbc comes from MS, I am still looking for something

[SQL] script for unidirectional database update

Hi, I need to periodically transfer the content of one db into another. The target db should be deleted before and there should be one ascii file containing the data, because there's a firewall between the two db's and file transfer ist the most simple thing to do. Does anyone have a script to au

[SQL] "'" in SQL INSERT statement

Hi, I have some data that I wish to transfer into a database using perl/DBI. Some of the data are strings containing the apostrophe "'" which I use as string delimiter. How can I put these into my database using the INSERT statement? Thanks, Markus

[SQL] #DELETED error when using Access 2000 as frontend

Hi, I tried to subscribe to pgsql-interfaces several times and received "user not found". I also searched the pgsql-interfaces archives, without success. So here is my problem. I want to use pg 7.x as a backend for a MS Access application. I linked a table via ODBC, using the newest ODBC driv

[SQL] using pg as a db backend in Access

Hi, I recently posted a problem with connecting to a pg db server from access. Now I found that the pg/odbc driver under windows lets choose between three protocols, 6.x, 6.y, 6.z. I am using pg 7.x as the db server. Could this be the problem? I managed to get the tables linked and shown, but

Re: [SQL] connecting to postgres server from Access

Hi Joel! > http://www.postgresql.org/users-lounge/index.html Ok, my fault... > > "cannot create index for the selected field" > > > > Then the linked table appears in the tables tab in Access. When trying > > to open it, I get ("Organisation" is the table to be linked): > > > > "ODBC error: >

Re: [SQL] connecting to postgres server from Access

d): "ODBC error: error while executing the query ERROR: Organisation: Permission denied (#1)" I have searched for Windows pg tools but I didn'*t find one. Please help, Markus Joel Burton wrote: > > On Wed, 10 Jan 2001, Markus Wagner wrote: > > > Hi, > > >

[SQL] connecting to postgres server from Access

Hi, I'm running a postgres db server on my linux machine. Someone else would like to build his frontends with MS Access and use the postgres server as backend. He installed the Pg/ODBC-Driver linked to at the pg web site. But when he tries to link in some table he always gets an error message

[SQL] postmaster: address already in use

Hi, my postmaster suddenly begins to make problems. It was running fine, but then: FATAL: StreamServerPort: bind() failed: Address already in use Is another postmaster already running on that port? If not, remove socket node (/tmp/.s.PGSQL.5432) and retry. /sys/svr/pgs/bin/postma

[SQL] JDBC: oid after insert

Hi, the JDBC docs coming with pgs 7 are not that detailed. How can I get the oid after an INSERT? Thanks, Markus

[SQL] create function - user permissions

Hi, one of my users wants to create functions using the C language, but pgs says "no permission". How can I permit the user to do this, while avoiding to give him root access rights? Thanks, Markus

[SQL] using OID as primary key

Hi, are there any disadvantages of using OID as the primary key for any table? What about referencing external tuples using their OIDs? e. g.: CREATE TABLE thistable ... field NUMERIC REFERENCES OtherTable (oid) Which data type should I use to reference OIDs? Will I run into problems later if

[SQL] referencing serials

Hello, which data type should be used to hold references to SERIALs in external tables? I tried to use SERIAL, but then a sequence is created for the referencing table. Markus

Re: [SQL] postmaster: init.d/start

Hi Daniel, thank you for your reply. There are some unanswered questions. Why does one have to consider multiple run levels? Doesn't run level 3 (network + X) implicate run level 2 (network)? So if I don't use XDM (or equivalent) and the system starts with a login shell only, it would start run

[SQL] postmaster: init.d/start

Hi, under SuSe Linux 6.3 there are different subdirectories rc.d for different runlevels. Each dir contains links to the start/stop scripts. The filenames contain numbers to indicate the position / the order of the services to start. Where (for which runlevel) and at what position should I link

[SQL] PG/DBI: 'NOTICE: UserAbortTransactionBlock and not in in-progress state'

Hi, I am using PG 6.5.1 and DBI. My Perl programs always print to stderr: "NOTICE: UserAbortTransactionBlock and not in in-progress state" or "NOTICE: EndTransactionBlock and not inprogress/abort state" I tried to do -> finish and -> commit after each query, but the messages still appear. An