Re: [SQL] How to represent a tree-structure in a relational database
Ron Peterson wrote: > > This structure is more 'normal' in the sense that nodes without children > (in a tree, the leaf nodes) don't have records in the edge table. Phghpth. Should have had my coffee first. The first data structure given would only have a null parent id for the root node, not all the leaf nodes. My mistake. Thought it might be politic to point that out before someone (correctly) called me an idiot. -Ron-
[SQL] system catalog info
The HTML programming documentation (e.g. http://www.postgresql.org/devel-corner/docs/programmer/pg-system-catalogs.htm) indicates that more extensive information about the system catalogs can be found in the "Reference Manual". Where can this reference manual be found? Or where can more extensive documentation about the system catalogues be found? -Ron-
Re: [SQL] Compiling "C" Functions
Tulio Oliveira wrote: > > I appreciate any "C" Function complete samples, including de command > line for > the compiler. I've attached a generic GNU make snippet for compiling .so files. Adjust to suite your tastes. Like my math textbooks used to say "writing the C code is trivial, and is left as an excercise for the reader." ;) CC = /usr/bin/gcc TARGET = debug #TARGET = final DFLAGS = -Wall -g FFLAGS = -Wall -O2 SFLAGS = -fpic -shared MYINCLUDES = -I/usr/local/include -I/usr/local/src/postgresql/src/include -I/usr/local/postgresql/include MYLIBS = -L/usr/local/lib -L/usr/local/postgresql/lib -lpq ifeq ($(TARGET),final) MYCFLAGS = $(FFLAGS) else MYCFLAGS = $(DFLAGS) endif %.so: $(CC) $(MYCFLAGS) $(MYINCLUDES) $(MYLIBS) $(*F).c -c -o $(*F).o $(CC) $(SFLAGS) $(*F).o -o $(*F).so [ -d $(TARGET) ] || mkdir $(TARGET) mv $(*F).so $(TARGET) rm *.o
Re: [SQL] Tree structure table normalization problem (do I need a trigger?)
Frank Joerdens wrote: > > In a recent thread (How to represent a tree-structure in a relational > database) I asked how to do a tree structure in SQL, and got lots of > suggestions (thanks!), of which I chose the one below: > > create table Category ( > CategoryID int4 not null primary key, > ParentCategoryID int4 not null REFERENCES Category (CategoryID), > CategoryName varchar(100) > ); > > The one described in Joe Celko's article (the one with the worm that > travels along the edge of the tree . . . ) seemed more evolved but > requires fairly complex SQL stuff, I thought, for simple operations that > are straighforward with the above model. SQL99 (which is what SQL3 became) defines a recursive data model that allows you to implement these types of structures. IBM's DB2 implements at least a subset of this standard (this is based on hearsay, not personal experience). Oracle also provides some SQL extensions to allow you to create recursive queries, but they are nonstandard (CONNECT BY, LEVELS, ...). I don't find any of the solutions to this problem using SQL92 satisfactory. Celko's tree structure can require updates to every node in the tree for operations on a single node. And once you start writing procedural code, you're obviating SQL itself. So for myself, I've basically decided to hold my horses and find other interesting things to do until the SQL99 standard finds widespread adoption. I realize this might not be a satisfactory answer, but if you can afford to wait, a better solution should be on the way. -Ron-
Re: [SQL] How to represent a tree-structure in a relational database
Ron Peterson wrote: > > CREATE TABLE category_edge ( > parent INTEGER > NOT NULL > REFERENCES category_node(id), > > child INTEGER > NOT NULL > REFERENCES category_node(id) > ); Just for the sake of anal-retentive completeness, I'd like to point out that you'd probably want an id field in this table also. Plus what the heck else am I going to do on Christmas break? ;) On a completely unrelated topic: getting the PostgreSQL discussions lists on a news server is great!!! I was overwhelmed with mail that I usually don't have time to deal with. Now when I have a chance, I just go see what's up on the news server. Excellent and thanks! -Ron-
Re: [SQL] How to represent a tree-structure in a relational database
Stuart Statman wrote:
>
> I would suggest, instead, to create a table that represents your hierarchy
> without adding columns. For example :
>
> create table Category (
> CategoryID int4 not null primary key,
> ParentCategoryID int4 not null REFERENCES Category (CategoryID),
> CategoryName varchar(100)
> );
Another possibility would be to use two tables to represent the data
structure.
CREATE SEQUENCE category_node_id_seq;
CREATE TABLE category_node (
nameTEXT
NOT NULL,
id INTEGER
DEFAULT NEXTVAL('category_node_id_seq')
PRIMARY KEY
);
CREATE TABLE category_edge (
parent INTEGER
NOT NULL
REFERENCES category_node(id),
child INTEGER
NOT NULL
REFERENCES category_node(id)
);
This structure is more 'normal' in the sense that nodes without children
(in a tree, the leaf nodes) don't have records in the edge table.
What either of these structures allow to do is create directed graph
structures. If you'd like to constrain this structure to be a tree, you
have to enforce that restriction with procedural code.
-Ron-
Re: [SQL] Looking for comments
Thomas SMETS wrote: > > > ISBN's have a checkdigit; it would be sensible to provide a > function to be used in a CHECK constraint to ensure that the > ISBN is valid. > Here's a URL with more information:http://www.isbn.spk-berlin.de/html/userman/usm4.htm. I've written a similar algorithm in C to create and verify checksums for eight digit codes. The final code is then 9 digits. I did this to help create codes that are the same length as a social security number. If you'd like a copy, let me know. -Ron-
[SQL] sql99 / sql3
I just recieved SQL in a Nutshell from O'Reilly. I bought the book because it covers the SQL99 standard. It's my understanding that this is the most recent SQL standard. Am I correct about this? What is the current status of SQL standards? What is the most recent approved standard, and what aspects of SQL are still under discussion? I ask, because I notice that the book doesn't mention recursive unions. In fact I don't see mention of unions at all, nevermind recursive unions, so this may simply be an oversight. However, since this is a feature I've really been looking forward to, I'm now wondering if I'm hoping in vain. What organization is currently responsible for drafting SQL standards? How can I obtain publications describing those standards? Is there anything comprehensive and definitive online? -Ron-
[SQL] finding foreign keys
Can anyone suggest a more elegant way of finding foreign keys than parsing the tgargs value returned by this query? I'd really rather do pure SQL, sans string parsing, if possible. -- Find tables and foreign keys CREATE VIEW foreignkeysa AS SELECT t.oid as toid, c1.relname AS relid, c2.relname AS constrrelid, t.tgargs AS tgargs FROMpg_trigger AS t, pg_class AS c1, pg_class AS c2 WHERE t.tgrelid=c1.oid AND t.tgconstrrelid=c2.oid AND t.tgfoid=1644; -Ron-
[SQL] Re: Is there anything like DESCRIBE?
Mike D'Agosta wrote: > > Hi, > >I have a number of empty tables and I want to get the column names and > data types with an SQL statement. I want to do this procedurally, not > interactively (so I can't use \d in psql). Postgres doesn't > support DESCRIBE... is there any other way to do this? src/tutorial/syscat.source has some examples you might like to study. -Ron-
[SQL] Re: Recursive select
Don't drive yourself crazy ( like me ;). You'll have to write some procedural code - sorry. I believe IBM's DB/2 supports recursive queries as defined by SQL3. Oracle provide a couple of non SQL standard clauses (CONNECT BY, LEVELS) to provide similar funcionality. -Ron- GPG and other info at: http://www.yellowbank.com/ Martin Smetak wrote: > > Hi all! > > Anyone know if it's possible to make a recursive select from a table ? > My problem: I got a table of "some categories" which all points to its > parrent one(tree)...shown below. And I want to select all names of > parrent categories of one child, lets say "fast[4]". Now I'm solving that > with > many SQL queries like : "SELECT main_id FROM cat WHERE id=4;"but I would > like to optimize this. > > Anyone can help or point me to a way ?? > > Thanks a lot,Minca > > Table : CAT > ID|Main_id|Name > = > 10Car > 21Crash > 31Wash > 43Fast > 51Second_hand > 60House > 73Slow > etc > > *(root)[0] > -Car[1] > -Crash[2] > -Wash[3] > -Fast[4] > -Slow[7] > -Second hand[5] > -House[6] > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] graphical interface - admin
On Thu, Jun 27, 2002 at 07:50:09PM +0800, q u a d r a wrote: > > What's the best open source GUI for DB administration? (postgres) Emacs. ;) -- Ron Peterson -o) 87 Taylor Street /\\ Granby, MA 01033 _\_v https://www.yellowbank.com/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] insert rule doesn't see id field
Two seperate problems, really, but first the SQL: CREATE SEQUENCE person_id_seq; CREATE TABLE person ( name_last VARCHAR( 50 ) NOT NULL, name_first VARCHAR( 50 ) NOT NULL, id INTEGER DEFAULT nextval( 'person_id_seq' ) PRIMARY KEY ); CREATE INDEX person_name_idx ON person ( name_last, name_first ); CREATE TRIGGER person_id_noup BEFORE UPDATE ON person FOR EACH ROW EXECUTE PROCEDURE noup( 'id' ); CREATE RULE person_insert AS ON INSERT TO person DO INSERT INTO person_log ( name_last, name_first, mod_type, person_id ) VALUES ( new.name_last, new.name_first, 'I', new.id ); (Problem 1) My insert rule creates a record in person_log just fine. It inserts values for all of the fields except person_id. Why doesn't new.id contain a value? Corresponding update and delete rules work as expected. (Problem 2) I thought that the idea behind noup was to protect single columns from update. However, when I apply the noup trigger as above, I can't update /any/ column. Is this the intended behaviour? e.g. directory=# select * from person; name_last | name_first | id ---++ Peterson | Ronald | 1 Humbert | Humbert| 2 (2 rows) directory=# update person set name_first='Ron' where name_first='Ronald'; NOTICE: id: update not allowed UPDATE 0 -- Ron Peterson -o) Network & Systems Manager /\\ Mount Holyoke College_\_v http://www.mtholyoke.edu/~rpeterso ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] insert rule doesn't see id field
BTW, PostgreSQL 7.2.1-2woody2 on Debian. -- Ron Peterson -o) Network & Systems Manager /\\ Mount Holyoke College_\_v http://www.mtholyoke.edu/~rpeterso ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] insert rule doesn't see id field
On Tue, Jan 07, 2003 at 11:01:08AM -0500, Tom Lane wrote: > > I thought that the idea behind noup was to protect single columns from > > update. However, when I apply the noup trigger as above, I can't > > update /any/ column. Is this the intended behaviour? > > Idly looking at the source code for contrib/noupdate/noup.c, I don't > believe that it has ever worked as advertised: it seems to reject any > non-null value for the target column, independently of whether the > value is the same as before (which is what I'd have thought it should > do). > > Is anyone interested in fixing it? Or should we just remove it? > If it's been there since 6.4 and you're the first person to try to use > it, as seems to be the case, then I'd have to say that it's a waste of > space in the distribution. I'm going to see if I can create this function. The issue I face is that I'm allowing certain clients to access parts of a PostgreSQL database on MS Access via ODBC. This means I can't really control how people may try to edit the data. Well, I could, by using MS Access security features, but I'd rather do what I can on the back end. If someone changes an ID field, then as long as foreign key contraints on other related tables are set to cascade or whatever, that won't be a problem. But what if someone updates an ID field to something higher than my current sequence? Then when the sequence hits that ID, it will crap out. Maybe just try again, but what if that happened to a bunch of records? Could be a pain. So that's the problem I'd like to prevent, for which I think this function would be useful. So I'll hack at it and see what I come up with. Might not happen immediately, though.. -- Ron Peterson -o) Network & Systems Manager /\\ Mount Holyoke College_\_v http://www.mtholyoke.edu/~rpeterso ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] insert rule doesn't see id field
On Thu, Jan 09, 2003 at 07:54:04PM -, [EMAIL PROTECTED] wrote:
> Sounds similar to the plpgsql example at:
>
> http://www.archonet.com/pgdocs/lock-field.html
>
> which silently discards changes. It's trivial (apart from quoting issues)
> to write a trigger generator to customise the above in plpgsql (see the
> Cookbook on techdocs.postgresql.org for examples)
Ah - I wish I saw that earlier. Thanks for pointer. OTOH, I learned
something...
FWIW (probably just a good laugh):
#include "executor/spi.h" /* this is what you need to work with SPI */
#include "commands/trigger.h" /* and triggers */
#include /* tolower () */
#include
extern Datum noupcols (PG_FUNCTION_ARGS);
/*
noupcols () -- revoke permission on column(s)
e.g.
CREATE FUNCTION noupcols ()
RETURNS opaque
AS '/usr/lib/postgresql/lib/noupcols.so'
LANGUAGE 'C';
CREATE TRIGGER person_noupcols
BEFORE UPDATE ON person
FOR EACH ROW
EXECUTE PROCEDURE noupcols( 'name_last', 'id' );
Based on code from contrib/noup.c
The approach adopted here is to set the values of all of the columns
specified by noupcols to their old values.
*/
PG_FUNCTION_INFO_V1 (noupcols);
Datum
noupcols (PG_FUNCTION_ARGS)
{
TriggerData *trigdata = (TriggerData *) fcinfo->context;
Trigger *trigger; /* to get trigger name */
Relationrel;/* triggered relation */
char**args; /* arguments: column names */
int ncols; /* # of args specified in CREATE TRIGGER */
int *colindices;/* array of column indices to modify */
Datum *oldcolvals;/* old column values */
HeapTuple oldtuple = NULL;/* tuple before being modified */
HeapTuple newtuple = NULL;/* new tuple after user-specified update */
HeapTuple newnewtuple = NULL; /* tuple to return, after restoring newtuple's
protected columns to their old values */
TupleDesc tupdesc;/* tuple description */
boolisnull; /* to know is some column NULL or not */
int ret;
int i;
if (!CALLED_AS_TRIGGER (fcinfo))
elog(ERROR, "noup: not fired by trigger manager");
if (TRIGGER_FIRED_FOR_STATEMENT (trigdata->tg_event))
elog (ERROR, "noup: can't process STATEMENT events");
if (TRIGGER_FIRED_BY_INSERT (trigdata->tg_event))
elog (ERROR, "noup: can't process INSERT events");
else if (TRIGGER_FIRED_BY_DELETE(trigdata->tg_event))
elog (ERROR, "noup: can't process DELETE events");
oldtuple = trigdata->tg_trigtuple;
newtuple = trigdata->tg_newtuple;
trigger = trigdata->tg_trigger;
rel = trigdata->tg_relation;
tupdesc = rel->rd_att;
ncols = trigger->tgnargs;
args = trigger->tgargs;
colindices = (int *) malloc (ncols * sizeof (int));
/* Connect to SPI manager */
if ((ret = SPI_connect()) < 0)
elog (ERROR, "noupcol: SPI_connect returned %d", ret);
/* Allocate space to place column values */
oldcolvals = (Datum*) palloc (ncols * sizeof (Datum));
/* For each column ... */
for (i = 0; i < ncols; i++)
{
/* get index of column in tuple */
colindices[i] = SPI_fnumber (tupdesc, args[i]);
/* Bad guys may give us un-existing column in CREATE TRIGGER */
if (colindices < 0)
{
elog (ERROR, "noupcols: there is no attribute %s in relation %s",
args[i],
SPI_getrelname (rel));
pfree (oldcolvals);
free (colindices);
SPI_finish ();
return PointerGetDatum (NULL);
}
/* Get previous value of column */
oldcolvals[i] = SPI_getbinval (oldtuple, tupdesc, colindices[i], &isnull);
}
/* Restore protected columns to their old values */
newnewtuple = SPI_modifytuple (rel, newtuple, ncols, colindices, oldcolvals, NULL);
pfree (oldcolvals);
free (colindices);
SPI_finish ();
if (SPI_result == SPI_ERROR_ARGUMENT) {
elog (ERROR, "noupcols: bad argument to SPI_modifytuple\n");
return PointerGetDatum (NULL);
}
if (SPI_result == SPI_ERROR_NOATTRIBUTE) {
elog (ERROR, "noupcols: bad attribute value passed to SPI_modifytuple\n");
return PointerGetDatum (NULL);
}
return PointerGetDatum (newnewtuple);
}
--
Ron Peterson -o)
Network & Systems Manager /\\
Mount Holyoke College_\_v
http://www.mtholyoke.edu/~rpeterso
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] insert rule doesn't see id field
On Wed, Jan 08, 2003 at 01:13:03PM -0500, Ron Peterson wrote: > On Tue, Jan 07, 2003 at 11:01:08AM -0500, Tom Lane wrote: > > > > I thought that the idea behind noup was to protect single columns from > > > update. However, when I apply the noup trigger as above, I can't > > > update /any/ column. Is this the intended behaviour? > > > > Idly looking at the source code for contrib/noupdate/noup.c, I don't > > believe that it has ever worked as advertised: it seems to reject any > > non-null value for the target column, independently of whether the > > value is the same as before (which is what I'd have thought it should > > do). > > > > Is anyone interested in fixing it? Or should we just remove it? > > If it's been there since 6.4 and you're the first person to try to use > > it, as seems to be the case, then I'd have to say that it's a waste of > > space in the distribution. > > I'm going to see if I can create this function. Well, I think I've thunk something up. Of course I'm happy to submit my modification for distribution or ridicule, as the case may be. Where should I submit this? I made a function noupcols() which takes one or more column names as arguments. The function then creates a new tuple by getting the old values for those columns, and then doing an SPI_modifytuple on the new tuple using the old values for those columns. I'm kind of flying by the seat of my pants here, so if anyone would care to critically review my code, by all means... -- Ron Peterson -o) Network & Systems Manager /\\ Mount Holyoke College_\_v http://www.mtholyoke.edu/~rpeterso ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] insert rule doesn't see id field
On Thu, Jan 09, 2003 at 04:50:56PM -0500, Ron Peterson wrote:
> colindices = (int *) malloc (ncols * sizeof (int));
Of course we should verify that malloc succeeded...
if (colindices == NULL) {
elog (ERROR, "noupcol: malloc failed\n");
SPI_finish();
return PointerGetDatum (NULL);
}
--
Ron Peterson -o)
Network & Systems Manager /\\
Mount Holyoke College_\_v
http://www.mtholyoke.edu/~rpeterso
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] insert rule doesn't see id field
On Thu, Jan 09, 2003 at 11:53:42PM -0500, Tom Lane wrote:
> Ron Peterson <[EMAIL PROTECTED]> writes:
> > On Thu, Jan 09, 2003 at 04:50:56PM -0500, Ron Peterson wrote:
> >> colindices = (int *) malloc (ncols * sizeof (int));
>
> > Of course we should verify that malloc succeeded...
>
> Actually, the correct answer is "you should not be using malloc() in
> backend functions". You should be using palloc, or possibly
> MemoryContextAlloc, either of which will elog if it can't get space.
>
> > if (colindices == NULL) {
> > elog (ERROR, "noupcol: malloc failed\n");
> > SPI_finish();
> > return PointerGetDatum (NULL);
> > }
>
> This is even more pointless. Control does not return from elog(ERROR),
> so the two following lines are dead code.
Thanks. Didn't know that.
Not that you're obligated to review my code in any way (i.e. ignore
this question if you have better things to do), but does the rest of
my code look o.k.? I was pretty pleased with myself that I figured
out how to modify a tuple w/ SPI, and might like to do more of the
same. I'd rather not develop bad habits, though...
--
Ron Peterson -o)
Network & Systems Manager /\\
Mount Holyoke College_\_v
http://www.mtholyoke.edu/~rpeterso
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] noupcol code cleanup
Well, I went through this again myself, and fixed a lot of stuff. I'm
going to drop this thread, but didn't want the last chunk of code I
posted to be so crappy. This is what I have come up with, FWIW:
#include "executor/spi.h" /* this is what you need to work with SPI */
#include "commands/trigger.h" /* and triggers */
#include "utils/lsyscache.h" /* for get_typlenbyval */
extern Datum noupcols (PG_FUNCTION_ARGS);
/*
noupcols () -- revoke permission on column(s)
e.g.
CREATE FUNCTION noupcols ()
RETURNS opaque
AS '/usr/lib/postgresql/lib/noupcols.so'
LANGUAGE 'C';
CREATE TRIGGER person_noupcols
BEFORE UPDATE ON person
FOR EACH ROW
EXECUTE PROCEDURE noupcols( 'name_last', 'id' );
Based on code from contrib/noup.c
The approach adopted here is to set the values of all of the columns
specified by noupcols to their old values.
*/
PG_FUNCTION_INFO_V1 (noupcols);
Datum
noupcols (PG_FUNCTION_ARGS)
{
TriggerData *trigdata = (TriggerData *) fcinfo->context;
Trigger *trigger; /* to get trigger name */
Relationrel;/* triggered relation */
char**args; /* arguments: column names */
int ncols; /* # of args specified in CREATE TRIGGER */
int *colindices;/* array of column indices to modify */
Datum *oldcolvals;/* old column values */
Datum *newcolval; /* new column value */
HeapTuple oldtuple = NULL;/* tuple before being modified */
HeapTuple newtuple = NULL;/* new tuple after user-specified update */
HeapTuple newnewtuple = NULL; /* tuple to return, after restoring newtuple's
protected columns to their old values */
TupleDesc tupdesc;/* tuple description */
boolisnull; /* to know is some column NULL or not */
Oid oid;/* is Datum of type ByVal? */
booltypByVal; /* is Datum of type ByVal? */
int16 typLen; /* Datum size */
int ret;
int i;
if (!CALLED_AS_TRIGGER (fcinfo))
elog(ERROR, "noup: not fired by trigger manager");
if (TRIGGER_FIRED_FOR_STATEMENT (trigdata->tg_event))
elog (ERROR, "noup: can't process STATEMENT events");
if (TRIGGER_FIRED_BY_INSERT (trigdata->tg_event))
elog (ERROR, "noup: can't process INSERT events");
else if (TRIGGER_FIRED_BY_DELETE(trigdata->tg_event))
elog (ERROR, "noup: can't process DELETE events");
oldtuple = trigdata->tg_trigtuple;
newtuple = trigdata->tg_newtuple;
trigger = trigdata->tg_trigger;
rel = trigdata->tg_relation;
tupdesc = rel->rd_att;
ncols = trigger->tgnargs;
args = trigger->tgargs;
colindices = (int *) palloc (ncols * sizeof (int));
/* Connect to SPI manager */
if ((ret = SPI_connect()) < 0)
elog (ERROR, "noupcol: SPI_connect returned %d", ret);
/* Allocate space to place column values */
oldcolvals = (Datum*) palloc (ncols * sizeof (Datum));
newcolval = (Datum*) palloc (sizeof (Datum));
/* For each column ... */
for (i = 0; i < ncols; i++)
{
/* get index of column in tuple */
colindices[i] = SPI_fnumber (tupdesc, args[i]);
/* Bad guys may give us un-existing column in CREATE TRIGGER */
if (colindices[i] == SPI_ERROR_NOATTRIBUTE) {
elog (ERROR, "noupcols: there is no attribute '%s' in relation '%s'",
args[i],
SPI_getrelname (rel));
}
/* Get previous value of column */
oldcolvals[i] = SPI_getbinval (oldtuple, tupdesc, colindices[i], &isnull);
*newcolval = SPI_getbinval (newtuple, tupdesc, colindices[i], &isnull);
/* need this for datumIsEqual, below */
oid = SPI_gettypeid (tupdesc, colindices[i]);
get_typlenbyval (oid, &typLen, &typByVal );
/* if an update is attempted on a locked column, post a
notification that it isn't allowed */
if (! datumIsEqual (oldcolvals[i], *newcolval, typByVal, typLen)) {
elog (NOTICE, "noupcols: attribute '%s' in relation '%s' is locked",
args[i],
SPI_getrelname (rel));
}
}
/* Restore protected columns to their old values */
newnewtuple = SPI_modifytuple (rel, newtuple, ncols, colindices, oldcolvals, NULL);
if (SPI_result == SPI_ERROR_ARGUMENT) {
elog (ERROR, "noupcols: bad argument to SPI_modifytuple\n");
}
if (SPI_result == SPI_ERROR_NOATTRIBUTE) {
elog (ERROR, "noupcols: bad attribute value
Re: [SQL] insert rule doesn't see id field
On Tue, Jan 07, 2003 at 11:01:08AM -0500, Tom Lane wrote:
> Ron Peterson <[EMAIL PROTECTED]> writes:
> > CREATE RULE person_insert AS
> > ON INSERT TO person
> > DO
> > INSERT INTO person_log ( name_last, name_first, mod_type, person_id )
> > VALUES ( new.name_last, new.name_first, 'I', new.id );
> > [where id is a serial column]
>
> > My insert rule creates a record in person_log just fine. It inserts
> > values for all of the fields except person_id. Why doesn't new.id
> > contain a value?
>
> This is a bug in 7.2.*. It's fixed in 7.3. However, your rule will
> still not work the way you would like, because rules are macros: the
> default expression for id will get evaluated once in the rule and once
> in your original query, leading to two different sequence numbers
> getting inserted.
I just installed 7.3.1. It works now, as you say, but it breaks if the
id field being updated by an insert rule references the id field it's
logging. And like you say, the rule also updates the sequence - not a
killer, but not so great.
I started writing a trigger. Meanwhile I'm just going to log updates
and deletes. After all, if a record has never been updated or deleted,
what's to audit?
One thing's tripping me up a bit while writing a trigger (in C - I like
C). I'd like the trigger function arguments to specify an attribute
mapping from the table being logged to the log table - e.g. logfun
('logtable', 'oldatt1', 'newatt1', 'oldatt2', 'newatt2', etc.). I
thought I'd be good and check that the types and field lengths match
before attempting to insert the log record. I can find out this info
for the relation pulling the trigger easy enought, but how would I go
about getting this info when all I have is the table/field name? I
could create and execute a SQL query something like the following, but
is that really the way to get at this info in C code?
select
relname,
attname,
typname,
typlen
from
pg_class c,
pg_attribute a,
pg_type t
where
c.oid=a.attrelid and
a.atttypid=t.oid and
get just the relevant record(s);
--
Ron Peterson -o)
Network & Systems Manager /\\
Mount Holyoke College_\_v
http://www.mtholyoke.edu/~rpeterso
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] Question about insert/update RULEs.
On Sun, Jan 09, 2005 at 06:45:54AM -0500, Dmitri Bichko wrote:
> CREATE TABLE "foo" (
> "foo_id"serial PRIMARY KEY,
> "type" varchar NOT NULL DEFAULT 'base' CONSTRAINT types CHECK
> (type IN ('base', 'bar'))
> "a" varchar NOT NULL,
> "b" varchar DEFAULT 'some text',
> );
>
> CREATE TABLE "foo_bar" (
> "foo_id" integer PRIMARY KEY CONSTRAINT foo REFERENCES foo (foo_id) ON
> DELETE CASCADE ON UPDATE CASCADE DEFERABLE,
> "c" varchar
> );
>
> CREATE VIEW "bar" AS
> SELECT f.foo_id, f.a, f.b, b.c
> FROM foo f JOIN foo_bar b USING(foo_id);
>
> CREATE RULE "bar_insert" AS ON INSERT TO "bar"
> DO INSTEAD (
> INSERT INTO "foo" (foo_id, type, a, b) VALUES
> (NEW.foo_id, 'bar', NEW.a, NEW.b);
> INSERT INTO "foo_bar" (foo_id, c) VALUES (NEW.foo_id,
> NEW.c);
> );
>
> The problem is that for the sequence to do the right thing, I have to
> select nextval first in a separate query and then pass it explicitely to
> INSERT INTO "bar" (which C:DBI does anyway, but I'd like to do better).
>
> If I were to do this:
> foo insert: foo_id = COALESCE(NEW.foo_id, nextval('foo_foo_id_seq')),
> foo_bar insert: foo_id = COALESCE(NEW.foo_id,
> currval('foo_foo_id_seq')),
I believe you may be trying to do something like the following:
CREATE RULE bar_insert AS
ON INSERT TO bar
DO INSTEAD
(
INSERT INTO
foo ( food_id, type, a, b )
VALUES
( nextval( 'foo_foo_id_seq' ), ... );
INSERT INTO
foo_bar ( foo_id, c )
VALUES
( currval( 'foo_foo_id_seq' ), ... );
);
(Which would mean there's no reason for view 'bar' to display foo_id)
Hmm, just noticed you defined foo.foo_id to be type 'serial', so you
could omit foo_id in the first INSERT.
> Will the currval() be guaranteed to be the same value that the nextval()
> got? I am not quite sure what the "scope" of currval() is.
The value of currval will be predictable within the current session.
I.E., if another session increments the sequence, the value returned by
currval in the current session won't change.
Best.
--
Ron Peterson
Network & Systems Manager
Mount Holyoke College
http://www.mtholyoke.edu/~rpeterso
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] simulating row ownership
On Fri, Jan 07, 2005 at 11:52:07AM -0500, Rick Schumeyer wrote: > I have a table where I want everyone to be able to be able to insert > and select. > But they should only be able to update and delete rows that they > "own". The table has a column indicating the owner. > What is the best way to accomplish this? I'm not real familiar with > rules, but it seems that I can do this with rules for update and > delete applied to the table. Using rules, you could do something like the following: CREATE TABLE test ( aname TEXT PRIMARY KEY ); INSERT INTO test ( aname ) VALUES ( 'aaa' ); INSERT INTO test ( aname ) VALUES ( 'yourusername' ); CREATE RULE lock_test_user_update AS ON UPDATE TO test WHERE old.aname = CURRENT_USER DO INSTEAD nothing; CREATE RULE lock_test_user_delete AS ON DELETE TO test WHERE old.aname = CURRENT_USER DO INSTEAD nothing; -- Ron Peterson Network & Systems Manager Mount Holyoke College http://www.mtholyoke.edu/~rpeterso ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] simulating row ownership
On Tue, Jan 11, 2005 at 08:38:21AM -0500, rpeterso wrote: > CREATE RULE lock_test_user_update > AS ON UPDATE TO test > WHERE old.aname = CURRENT_USER > DO INSTEAD nothing; > > CREATE RULE lock_test_user_delete > AS ON DELETE TO test > WHERE old.aname = CURRENT_USER > DO INSTEAD nothing; For your example, these rules should say !=, of course... -- Ron Peterson Network & Systems Manager Mount Holyoke College http://www.mtholyoke.edu/~rpeterso ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] mail + rfc822, rfc2822 + schema
On Tue, Jan 18, 2005 at 05:05:55PM +0200, Achilleus Mantzios wrote: > i am thinking of doing a remote MUA web-based system, based on > postgresql. > ... > So, i'd like to know if any of you has designed a schema serving as an > mail storage. If anything of caution arrose to you, and if you had > something to recommend. You might want to look at dbmail. http://www.dbmail.org/index.php?page=overview -- Ron Peterson Network & Systems Manager Mount Holyoke College http://www.mtholyoke.edu/~rpeterso ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] create temp table in rule
Is it possible to create a temporary table within a rule? I get a syntax error near 'CREATE TEMPORARY TABLE' if I try to do the following: CREATE RULE id_insert_rule AS ON INSERT TO id_insert DO INSTEAD ( CREATE TEMPORARY TABLE id_temp ( LIKE id ) ON COMMIT DELETE ROWS; INSERT INTO id_temp ( username, pass_md5, pass_sha1 ) VALUES new.username, encode( digest( new.password, 'md5' ), 'hex' ), encode( digest( new.password, 'sha1' ), 'hex' ); DELETE FROM id USING id_temp WHERE id.username = id_temp.username; INSERT INTO id SELECT * FROM id_temp; ); As you can see, I'm trying to create a simple 'insert or update' rule. -- Ron Peterson Network & Systems Manager Mount Holyoke College http://www.mtholyoke.edu/~rpeterso ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] on insert rule with default value
My rule below does not insert the the same uuid value into the test_log table as is created in the test table when I insert a new value. I know I've worked through this before, but I'm not remembering why this is. What's a right way to do this? create table test ( anid uuid not null default encode( gen_random_bytes( 16 ), 'hex' )::uuid primary key, value text ); create table test_log ( anid uuid, value text, op text, attime timestamp with time zone ); create rule test_rule_a as on insert to test do ( insert into test_log ( anid, value, op, attime ) values ( new.anid, new.value, 'insert', now() ) ); -- Ron Peterson Network & Systems Administrator Mount Holyoke College http://www.mtholyoke.edu/~rpeterso -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] on insert rule with default value
2012-02-21_15:51:30-0500 Ron Peterson : > My rule below does not insert the the same uuid value into the test_log > table as is created in the test table when I insert a new value. I know > I've worked through this before, but I'm not remembering why this is. > What's a right way to do this? Obviously I can use a trigger function. I'm mostly wondering if there are any tricks to accomplishing this with rules (I like the simple syntax). I suspect the problem here is that 'new' on insert refers to the function used to calculate the new value, /not/ the new value that is actually inserted into the table. There are probably reasons for that; but it would seem nicer to refer to the actual new table value rather than the expression used to calculate it. My 2c. -Ron- > create table test ( > anid > uuid > not null > default encode( gen_random_bytes( 16 ), 'hex' )::uuid > primary key, > value > text > ); > > create table test_log ( > anid > uuid, > value > text, > op > text, > attime > timestamp with time zone > ); > > create rule test_rule_a as > on insert to test do ( > insert into test_log ( anid, value, op, attime ) > values ( new.anid, new.value, 'insert', now() ) > ); -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
