[SQL] Table Design Questions
Hello, I am trying to come up with an efficient table design that describes a fantasy character that meets the following criteria: * Character "attributes" may somewhat frequently be added and removed with time. (e.g.: I may decide later on to add a boolean attribute CanSwim, or remove it all together as code evolves in production.) * To complicate things, attribute values may represent numbers, boolean, or a selection of one or more enumerated types. * Attributes will be frequently searched against to find characters that meet attribute criteria. For the time being I have decided to structure the Attributes table as something similar to the following: CREATE TABLE ATTRIBUTES ( CHAR_ID INT PRIMARY KEY NOT NULL, ATTRIB_TYPE_ID INT NOT NULL, ATTRIB_VALUE INT, CONSTRAINT ATTRIB_TYPE_ID_FK FOREIGN KEY (ATTRIB_TYPE_ID) REFERENCES ATTRIB_TYPES (ATTRIB_TYPE_ID) ); CREATE TABLE ATTRIB_TYPES ( ATTRIB_TYPE_ID INT PRIMARY KEY NOT NULL, ATTRIB_TYPE VARCHAR(20) NOT NULL, ATTRIB_NAME VARCHAR(20) UNIQUE NOT NULL, ); So here are my Questions: If ATTRIBUTES.VALUE could be an Integer, Boolean, or Enumeration, what is a clean way to represent this in the ATTRIB_TYPES table? My initial thought was to just set ATTRIB_TYPES.ATTRIB_TYPE to "INT", "BOOLEAN", "ENUM", etc... and just leave it up to the front end to worry about what these "types" mean and what is considered valid data. I was thinking that I could expand upon the ATTRIB_TYPES table to include a MIN, MAX, and Enum array columns in order to encapsulate what is considered valid values/ranges for ATTRIBUTES.VALUE. Does this seem like a good use of inheritance off of the ATTRIB_TYPES table? -- Please forgive my naiveté, but, what are typical solutions for dealing with enumerated types or sets? For example, a set of (Human | Elf | Dwarf | Gnome) where the ATTRIBUTES.VALUE could be any combination of the above. I realize I could, in this case, think of ATTRIBURES.VALUE as a binary value where a character is both a Human & Elf (half elf) is (1100), but that just doesn't sit right with me. Is there a better way to do this? Perhaps by making ATTRIBUTES.VALUE an array? If so, would I be correct in assuming this would add a performance hit to searches? (And please don't tell me to just add half elf to the set :). Much appreciated, Chad __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.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
Re: [SQL] SQL function parse error ?
Robert, my dear fellow... How about checking your facts before contradicting anyone ? Shame on you ! Have you actually tried to do a SELECT* from foo ? Pathetic ! Try it out, if that won't work on Oracle, MSSQL, PostgreSql I'll buy everyone on this list a chase of Crystal. Robert, even select*from errors; works on all three of them ! (Sorry about the colors, pasted from TOAD). Here's psql for instance: db=# SELECT * from prod ; id | name | price | group_id +--++-- 4 | some product | 55.00 |3 6 | MyPC | 300.00 |3 (2 rows) db=# SELECT*from prod ; id | name | price | group_id +--++-- 4 | some product | 55.00 |3 6 | MyPC | 300.00 |3 (2 rows) There you go. Now go sit in the corner ! :) Also, as I tried to make it quite clear, the point is not whether Oracle or mysql allow the use of $ in operators, but whether you have to write cumbersome syntax to get things working. And the answer is definitely _no_. My point here is that common sense (and the use of $1, $2,... with operator > is going come up a lot, opposed to the user-defined operator >$, which takes precedence when parsing a special case of the SQL command) should prevail over backwards compat. Loot at C++ for instance, the standard broke a lot of C++ apps that were written poorly. It seems that - strangely - instead of trying to acknowledge not necessarily incorrect but awkward behavior, some people on this list have tried to put me down. What's even more scary is receiving answers like "SQL queries are like bash commands", or "SELECTXFROMY is not valid - whitespace matters" (when in fact I was simply pointing out that i==3 and i == 3 should be parsed alike), or Robert's claim that SELECT* from ... is invalid SQL. The good news is some people seem to have gotten the point and are doing something about it - and this makes me feel like maybe, maybe I've helped the community just a little bit. After all, we all want to see postgresql up there where it belongs. That being said, I do hope that superficial replies trying to prove me wrong will stop, as they actually don't help anyone. Regards, = Radu-Adrian Popescu CSA, DBA, Developer Aldratech Ltd. - Original Message - From: "Robert Treat" <[EMAIL PROTECTED]> To: "Radu-Adrian Popescu" <[EMAIL PROTECTED]> Cc: "Achilleus Mantzios" <[EMAIL PROTECTED]>; "Tom Lane" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, January 09, 2003 10:44 PM Subject: Re: [SQL] SQL function parse error ? On Thu, 2003-01-09 at 11:29, Radu-Adrian Popescu wrote: > What i'm saying is that i know that some of my colleagues, nice guys for > that matter, and good programmers, will come screaming > to me "what's with the b.s. error ?!?", and when i'll tell them that the sql > parser belives that's an inexisting operator, they'll start > cursing at it, just like i did. > Does oracle or mysql or whichever db you like allow the use of $ in user defined operators? If so, how do they know the difference? For what it's worth, some policy should be enforced, because it shouldn't > matter how many spaces you put between the operator > and the operand, as writing SELECT * is the same as SELECT > *. > I rest my case. > Thats an invalid comparison. The problem is not that foo > $1 doesn't work, as your example put forth. The problem is that foo>$1 doesn't work, which by comparison would be SELECT* which would also not work. Robert Treat ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] insert rule doesn't see id field
To everyone interested, check out Tom Lane's and Bruce's comments on pgsql-hackers: http://archives.postgresql.org/pgsql-hackers/2003-01/msg00446.php There seems to be some consensus towards removing $ from the list of allowed operator characters. Regards, = Radu-Adrian Popescu CSA, DBA, Developer Aldratech Ltd. - Original Message - From: "Ron Peterson" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, January 09, 2003 9:12 PM Subject: 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 ---(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] insert rule doesn't see id field
I'm extremely sorry about the post in this thread ! Had a brain cramp, my appologies. Should have been Re: [SQL] SQL function parse error. Terribly sorry again ! = Radu-Adrian Popescu CSA, DBA, Developer Aldratech Ltd. - Original Message - From: "Radu-Adrian Popescu" <[EMAIL PROTECTED]> To: "Ron Peterson" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, January 10, 2003 11:31 AM Subject: Re: [SQL] insert rule doesn't see id field To everyone interested, check out Tom Lane's and Bruce's comments on pgsql-hackers: http://archives.postgresql.org/pgsql-hackers/2003-01/msg00446.php There seems to be some consensus towards removing $ from the list of allowed operator characters. Regards, = Radu-Adrian Popescu CSA, DBA, Developer Aldratech Ltd. - Original Message - From: "Ron Peterson" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, January 09, 2003 9:12 PM Subject: 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 ---(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 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] SQL function parse error ?
On Fri, 2003-01-10 at 04:13, Radu-Adrian Popescu wrote: > > Robert, my dear fellow... > > How about checking your facts before contradicting anyone ? Shame on you ! > Have you actually tried to do a SELECT* from foo ? Pathetic ! At least you started out all nice and flowery... > Try it out, if that won't work on Oracle, MSSQL, PostgreSql I'll buy > everyone on this list a chase of Crystal. > Robert, even > select*from errors; > works on all three of them ! (Sorry about the colors, pasted from TOAD). > > There you go. Now go sit in the corner ! :) Would an acceptable defense be that select*fromfoo doesn't work? Bah, off to the corner I go... > Also, as I tried to make it quite clear, the point is not whether Oracle or > mysql allow the > use of $ in operators, but whether you have to write cumbersome syntax to > get things working. And the > answer is definitely _no_. But the point I was trying to make is that maybe the reason the answer is no is that they don't support $ in operators. You'll note that a clause like mytime My point here is that common sense (and the use of $1, $2,... with operator > > is going > come up a lot, opposed to the user-defined operator >$, which takes > precedence when parsing a special > case of the SQL command) should prevail over backwards compat. Loot at C++ > for instance, the standard > broke a lot of C++ apps that were written poorly. > Would you suggest the parser should assume >$1 equals > $1? That seems likely to break a lot of cases where >$ was being used. OTOH, if your saying that support of >$1 is more important than support of >$ 1 that's a different argument. That's what you are saying (afaict) and that's also the path that the developers have taken in trying to resolve the issue. > It seems that - strangely - instead of trying to acknowledge not necessarily > incorrect but awkward behavior, > some people on this list have tried to put me down. I think people were trying to explain to you the reasons for the current behavior, at least that's what I was trying to attempt to do. > What's even more scary is receiving answers like "SQL queries are like bash > commands", > or "SELECTXFROMY is not valid - whitespace matters" (when in fact I was > simply pointing out that > i==3 and i == 3 should be parsed alike), or Robert's claim that SELECT* from > ... is invalid SQL. > yeah, my bad on that one. but your argument was still a non starter because we aren't debating support for > $1 (like in your example), but support for >$1. Actually you should be thanking in me, since while my supposition was wrong, my example helps bolster your case somewhat. :-) > The good news is some people seem to have gotten the point and are doing > something about it - and this > makes me feel like maybe, maybe I've helped the community just a little bit. > After all, we all want to see > postgresql up there where it belongs. > > That being said, I do hope that superficial replies trying to prove me wrong > will stop, as they actually don't help > anyone. > Maybe I need to re-read some of the other posts, but I think your taking this too personally. My email was simply trying to help frame the issue properly, because I saw you making an invalid argument in your own defense. Furthermore you need to realize that when someone makes a claim that a certain feature needs to work in a different fashion, or needs to be added at the expense of another feature, that it is only natural and a good thing that the proposal be given a little scrutiny to make sure it stands up. At this point yours does so in my book, though I still would like to see answered is whether oracle or others support >$ as an operator, or if the sql spec has anything to say on the matter. Robert Treat ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] SQL function parse error ?
Robert Treat <[EMAIL PROTECTED]> writes: > ... I still > would like to see answered is whether oracle or others support >$ as an > operator, or if the sql spec has anything to say on the matter. The SQL spec does not have the concept of user-definable operators at all, so it provides no useful guidance about the set of characters that ought to be allowed in operator names (except, obviously, that we must include every character actually used in a SQL-standard operator). Oracle reportedly treats $ as an identifier character, so I don't think they could consider it an operator character. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Table Design Questions
> Hello, > > I am trying to come up with an efficient table design > that describes a fantasy character that meets the > following criteria: Apologies for only dealing with the last part of your query - busy at the moment. I'll try and follow up in more detail later. > CREATE TABLE ATTRIBUTES ( > CHAR_ID INT PRIMARY KEY NOT NULL, > ATTRIB_TYPE_ID INT NOT NULL, > ATTRIB_VALUE INT, > CONSTRAINT ATTRIB_TYPE_ID_FK FOREIGN KEY > (ATTRIB_TYPE_ID) REFERENCES ATTRIB_TYPES > (ATTRIB_TYPE_ID) > ); > > CREATE TABLE ATTRIB_TYPES ( > ATTRIB_TYPE_ID INT PRIMARY KEY NOT NULL, > ATTRIB_TYPE VARCHAR(20) NOT NULL, > ATTRIB_NAME VARCHAR(20) UNIQUE NOT NULL, > ); > Please forgive my naiveté, but, what are typical > solutions for dealing with enumerated types or sets? > > For example, a set of (Human | Elf | Dwarf | Gnome) > where the ATTRIBUTES.VALUE could be any combination of > the above. In this case just multiple entries in the ATTRIBUTES table with the same CHAR_ID and ATTRIB_TYPE_ID. char01, race, human char01, race, elf (of course, I've used the text representation of the actual numbers above) > I realize I could, in this case, think of > ATTRIBURES.VALUE as a binary value where a character > is both a Human & Elf (half elf) is (1100), but that > just doesn't sit right with me. Is there a better way > to do this? Perhaps by making ATTRIBUTES.VALUE an > array? If so, would I be correct in assuming this > would add a performance hit to searches? If you're doing it properly, you probably want to be able to have something like (human 75%,elf 25%) which would mean adding a "percentage" or "multiplier" column to your ATTRIBUTE table. Or you could classify the different races/species with their own attribute type. So - you'd either have ATTRIBUTES (with new column) == char01, race, human, 0.75 char01, race, elf, 0.75 or ATTRIBUTES (existing columns) == char01, ishuman, 75 char01, iself, 25 You might want the extra column anyway, then you could have: char01, shortsword, attack, 40 char01, shortsword, parry, 30 > (And please don't tell me to just add half elf to the > set :). Of course, you could just add half-elf to the set ;-) - Richard Huxton ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
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] Table Design Questions
Chad, > I am trying to come up with an efficient table design > that describes a fantasy character that meets the > following criteria: Believe it or not, this is the first "D&D" question I've seen on this list. > CREATE TABLE ATTRIBUTES ( > CHAR_ID INT PRIMARY KEY NOT NULL, > ATTRIB_TYPE_ID INT NOT NULL, > ATTRIB_VALUE INT, > CONSTRAINT ATTRIB_TYPE_ID_FK FOREIGN KEY > (ATTRIB_TYPE_ID) REFERENCES ATTRIB_TYPES > (ATTRIB_TYPE_ID) > ); > > CREATE TABLE ATTRIB_TYPES ( > ATTRIB_TYPE_ID INT PRIMARY KEY NOT NULL, > ATTRIB_TYPE VARCHAR(20) NOT NULL, > ATTRIB_NAME VARCHAR(20) UNIQUE NOT NULL, > ); I do something similar a lot with User Defined Fields. Generally for UDFs I use a TEXT field to hold the data, setting up something like this: CREATE TABLE udfs ( udf_id SERIAL PRIMARY KEY, udf_format VARCHAR(30), udf_validateTEXT, udf_listINT FOREIGN KEY udf_lists (list_id) ); Where udf_format is a builtin or custom data type (INT, BOOLEAN, money, NUMERIC, TEXT, phone, e-mail, etc.), and udf_validate is a regexp to additionally validate the value. Based on the information on this table, you can write a custom function which formats each attribute as it comes out of the table based on the reference table. Hope that helps, half-elf! -Josh ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
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 passed to SPI_modifytuple\n");
}
pfree (oldcolvals);
pfree (newcolval);
pfree (colindices);
SPI_finish ();
return PointerGetDatum (newnewtuple);
}
--
Ron Peterson -o)
Network & Systems Manager /\\
Mount Holyoke College_\_v
[SQL] function does not exist
I am using postgresql version 7.2.3, and have the following situation.
When I attempt to add a function, I get the error CreateTrigger: function
mem_leveled() does not exist. Using the function in psql (i.e. SELECT
mem_leveled('fubar', 4, '2002/12/30 10:09:00 GMT'); ) works fine, I just
can't create a trigger for it.
I know its something stupid I'm doing (or not doing).
CREATE TABLE members (
name CHARACTER VARYING(256),
level smallint,
date_updated timestamptz,
... other stuff ...
)
CREATE TABLE mem_history (
name CHARACTER VARYING(256),
level smallint,
date_achieved timestamptz
)
CREATE FUNCTION mem_leveled (varchar, smallint, timestamptz) RETURN BOOLEAN
AS
'DELCARE
mem_lvl RECORD;
BEGIN
SELECT INTO mem_lvl * FROM mem_history WHERE name = $1 AND level = $2;
IF NOT FOUND THEN
INSERT INTO mem_history VALUES ($1, $2, $3);
END IF;
RETURN TRUE;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER update_mem_level AFTER INSERT OR UPDATE
ON members FOR EACH ROW
EXECUTE PROCEDURE mem_leveled('name', 'level', 'date_updated');
--
Dave A.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] function does not exist
Quoting from http://www.postgresql.org/idocs/index.php?plpgsql-trigger.html :
PL/pgSQL can be used to define trigger procedures. A trigger procedure
is created with the CREATE FUNCTION command as a function with no
arguments and a return type of OPAQUE. Note that the function must be
declared with no arguments even if it expects to receive arguments
specified in CREATE TRIGGER --- trigger arguments are passed via
TG_ARGV, as described below.
So, you need to write a trigger version of your function, or write a
trigger wrapper that pulls out the args and calls your work function.
Ross
On Fri, Jan 10, 2003 at 05:22:47PM -0500, Dave A. wrote:
> I am using postgresql version 7.2.3, and have the following situation.
>
> When I attempt to add a function, I get the error CreateTrigger: function
> mem_leveled() does not exist. Using the function in psql (i.e. SELECT
> mem_leveled('fubar', 4, '2002/12/30 10:09:00 GMT'); ) works fine, I just
> can't create a trigger for it.
>
> I know its something stupid I'm doing (or not doing).
>
> CREATE TABLE members (
> name CHARACTER VARYING(256),
> level smallint,
> date_updated timestamptz,
> ... other stuff ...
> )
>
> CREATE TABLE mem_history (
> name CHARACTER VARYING(256),
> level smallint,
> date_achieved timestamptz
> )
>
> CREATE FUNCTION mem_leveled (varchar, smallint, timestamptz) RETURN BOOLEAN
> AS
> 'DELCARE
> mem_lvl RECORD;
> BEGIN
> SELECT INTO mem_lvl * FROM mem_history WHERE name = $1 AND level = $2;
> IF NOT FOUND THEN
> INSERT INTO mem_history VALUES ($1, $2, $3);
> END IF;
> RETURN TRUE;
> END;
> ' LANGUAGE 'plpgsql';
>
> CREATE TRIGGER update_mem_level AFTER INSERT OR UPDATE
> ON members FOR EACH ROW
> EXECUTE PROCEDURE mem_leveled('name', 'level', 'date_updated');
>
> --
> Dave A.
>
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] function does not exist
Dave, A trigger function does not take variable parameters, gets its data from the NEW or OLD records, and returns OPAQUE with the RETURN NEW statement. Please check out the documentation on writing PL/pgSQL triggers under Procedural Languages in the online docs. -Josh Berkus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
