Re: [GENERAL] Re: c function

[Richard Harvey Chapman]

On Fri, 22 Sep 2000, K Parker wrote:

  Question: Why isn't 12 printed after the second SELECT statement? 
 
 It's the well-known (?) problem that if any function parameter is
 NULL, they are all set to NULL.  IIRC someone is working on fixing
 this...

a big thanks to you and Tom for the updates.

a note though: there are no problems with the parameters that I can see.
They're passed correctly to the c function, it's just the return type
that's messed up.

I suppose we'll just have to use 'NULL'.

BTW Is it possible to use a c-function with a prototype like the following
in postgres?

int myfunc (int id, ...);


R.

[GENERAL] psql \i

[Richard Harvey Chapman]

I have a text file of SQL commands which contains tabs.  I used to be able
to import it using psql using "\i file.sql".  Now, I've upgraded to 7.0.2
and it treats the file as if I'm entering it all in manually.  It treats
the tabs as completion commands.

CREATE TABLE alpha (
TABnumTABSERIAL PRIMARY KEY
);

will return a syntax error at PRIMARY because it sees the following:

CREATE TABLE alpha (
numSERIAL PRIMARY KEY
);

Is there something new that I have to do?

(besides converting the tabs -- if all else fails, I'll do that)

R.

Re: [GENERAL] Triggers with arguments

[Richard Harvey Chapman]

If I understand correctly, you have something like this:

CREATE TABLE rec_* (
num   integer primary key;
other ...
);

CREATE TABLE notes (
name   CHAR(20),
numINTEGER,
note   VARCHAR(200),
   PRIMARY KEY(name, num)
);

So, you have many different tables like rec_*, and one notes table that
can refer to them all.

Perhaps then, you want something like this:

CREATE TABLE notes (
name   CHAR(20),
numINTEGER REFERENCES rec_* 
 ON UPDATE CASCADE
 ON DELETE CASCADE,
note   VARCHAR(200),
   PRIMARY KEY(name, num)
);

'cept I just realized that a column can't reference multiple tables (can
it?).  Anyway, I found the above idea in Bruce's book in Chapter 14,
"Modification of Primary Key Row."

If I'm reading it correctly, deleting the record from rec_*, should cause
the corresponding record in notes to be deleted as well.

perhaps this'll help,

R.




On Wed, 12 Jul 2000, Scott Holmes wrote:

 I'm afraid I just don't see how this is done without being able to pass 
 arguments to the procedure or actually running an additional SQL statement 
 within the trigger:
   
   I have a "notes" table that is potentially linked to records in many other 
 tables.  The primary key is created from 1) the name of the table, and 2) the 
 primary key value of that table.  When one of these records, with notes, is 
 deleted, I need to make sure that the notes records are deleted as well.
 
 I've been playing around with this and perhaps my code that doesn't work will 
 illustrate what I need to accomplish and perhaps one of you kind readers will 
 show me just how to do it.
 
 CREATE FUNCTION del_stxnoted () RETURNS opaque AS '
   DECLARE
 fname alias for $1;
 rkey alias for $2;
   BEGIN
 delete from stxnoted where filename = fname
and record_key = rkey;
   END;'
 LANGUAGE 'plpgsql';
 
 
 create trigger del_location_trig
 after delete
   on location
   for each row
 execute procedure del_stxnoted("location", 'old.wher');
 
 Postgres will not create this trigger as it does not recognize the function 
 del_stxnoted as actually existing.
 
 
 I am attempting to convert a large suite of applications that currently run on 
 an Informix database.  The syntax for this is
 
 create trigger del_location delete on location referencing
 old as pre_del
 for each row
 (
 delete from stxnoted  where ((filename = 'location' ) AND (record_key
 = pre_del.wher ) ) );
 
 
 Thanks,  Scott
 
 
 
 

[GENERAL] pg_atoi()

[Richard Harvey Chapman]

Is there a reason why pg_atoi() was programmed to fail if the entire
input string is not valid?
i.e. "109" yields 109, but "109 apples" yields an error.

Snippet from pg_atoi() in src/backend/utils/adt/numutils.c:
 
l = strtol(s, badp, 10);

if (errno)   /* strtol must set ERANGE */
elog(ERROR, "pg_atoi: error reading \"%s\": %m", s);
if (badp  *badp  (*badp != c))
elog(ERROR, "pg_atoi: error in \"%s\": can\'t parse
\"%s\"", s, badp);

 
Thanks,

R.

[GENERAL] Comments with embedded single quotes

[Richard Harvey Chapman]

/* log will probably just be a table for Saruman's use only. */

Are single quotation marks not allowed in comments?

test2=# /* John's cat is fat. */
test2'#
test2'# '*/
test2-# ;
ERROR:  Unterminated quoted string
test2=#

I have the SQL + comments for creating my database in files that are just
imported in psql.

curious,

R.