[SQL] Column Types
If in MySQL i'm using type EMUN what type in Postgres?
Re: [SQL] Column Types
On Sun, 2003-09-14 at 07:49, Muhyiddin A.M Hayat wrote:
> If in MySQL i'm using type EMUN what type in Postgres?
Use a CHECK constraint:
CREATE TABLE xxx (
...
colourTEXT CHECK (colour IN ('red', 'green', 'blue')),
...
);
--
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
"But without faith it is impossible to please him; for
he that cometh to God must believe that he is, and
that he is a rewarder of them that diligently seek
him."Hebrews 11:6
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] A generic trigger?
On Sunday 14 September 2003 02:13, ow wrote:
> Hi,
>
> Am looking for a way to minimize the amount of fuctions that support
> triggers. E.g., there's "company" and "company_backup" tables. Update
> trigger on the "company" table will put a record in the "company_backup"
> table whenever "company" record is updated.
>
> The problem is that there's quite a few other tables for which similar
> backup logic has to be done (e.g. "custormer" and "customer_backup", etc).
> The backup logic is the same, only structure of the tables changes.
>
> Is there a way to write a generic trigger/function that would deal with
> backup regardless of the table structure?
>
> Thanks in advance.
>
>
>
Yes it is possible and I've done it. The reason I'm not using it is because I
wrote it in Pl/Python and if you attach the same trigger to more than one
table in the same transaction pg/python (actually the entire server crashes
but thats not the point) crashes. Well it did when I last tested it in early
versions. I'm still thinking of getting around to rewriting it in a language
without this bug, since nobody sounds like they are going to fix it. C might
be best!
This version inserts all the history in the same table. But since its broke
anyway changing it to insert into different tables should not be too
difficult.
There are some scripting languages where somthing don't work hense why I
chose pl/python The trigger/function is below although it should be in
the archives somwhere as well. Full problem with it can be seen of Bugs
Peter Childs
-- CREATE TABLE history ( tab textfieldtext
action textbefore text
aftertextoccured timestamp without time zone key
text who text );
DROP INDEX history_tab;
DROP INDEX history_tab_field;
DROP INDEX history_tab_key;
DROP INDEX history_tab_who;
DROP INDEX history_who;
CREATE INDEX history_tab on history(tab);
CREATE INDEX history_tab_field on history(tab,field);
CREATE INDEX history_tab_key on history(tab,key);
CREATE INDEX history_tab_who on history(tab,who);
CREATE INDEX history_who on history(who);
CREATE OR REPLACE FUNCTION history_update() RETURNS TRIGGER AS '
if TD["event"] == "INSERT":
lookup = "new"
elif TD["event"] == "DELETE":
lookup = "old"
else:
lookup = "new"
p = plpy.execute(" SELECT CASE i.indproc WHEN (''-''::pg_catalog.regproc) THEN
a.attname ELSE SUBSTR(pg_catalog.pg_get_indexdef(attrelid), POSITION(''('' in
pg_catalog.pg_get_indexdef(attrelid))) END as pkey, a.atttypid::int,
c2.relname FROM pg_catalog.pg_class c, pg_catalog.pg_class c2,
pg_catalog.pg_index i, pg_catalog.pg_attribute a WHERE c.oid = " +
TD["relid"] + " AND c.oid = i.indrelid AND i.indexrelid = c2.oid and
a.attrelid = i.indexrelid and NOT a.attisdropped and i.indisprimary ORDER BY
i.indisprimary DESC, i.indisunique DESC, c2.relname;")
if len(p) > 0:
pkey = TD[lookup][p[0]["pkey"]]
ppkey = p[0]["pkey"]
else:
pkey = ""
ppkey = ""
rel = plpy.execute("select relname from pg_class where oid=" + TD["relid"] +
";")
relname = rel[0]["relname"]
plan = plpy.prepare("INSERT INTO history
(tab,field,action,before,after,occured,who,key) values
($1,$2,$3,$4,$5,now(),user,$6);",["text","text","text","text","text","text"])
if TD["event"] == "INSERT":
old = ""
new = pkey
plpy.execute(plan,[relname,ppkey,TD["event"],old,new,pkey])
else:
for key in TD[lookup].keys():
dont = 0
if TD["event"] == "INSERT":
old = ""
new = TD["new"][key]
if new == None:
dont = 1
elif TD["event"] == "UPDATE":
old = TD["old"][key]
new = TD["new"][key]
else:
old = TD["old"][key]
new = ""
if old == None:
old = "Null"
if new == None:
new = "Null"
if new == old:
dont = 1
if not(dont):
plpy.execute(plan,[relname,key,TD["event"],old,new,pkey])
' LANGUAGE 'plpython';
CREATE TRIGGER history_update AFTER INSERT OR UPDATE OR DELETE ON
account_history
FOR EACH ROW EXECUTE PROCEDURE history_update();
---(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] A generic trigger?
--- Peter Childs <[EMAIL PROTECTED]> wrote: > Yes it is possible and I've done it. The reason I'm not using it is because > I > wrote it in Pl/Python and if you attach the same trigger to more than one > table in the same transaction pg/python (actually the entire server crashes > but thats not the point) crashes. Well it did when I last tested it in early > versions. I'm still thinking of getting around to rewriting it in a language > without this bug, since nobody sounds like they are going to fix it. C might > be best! [snip] Hi, In my case, "company" and "company_backup" tables have the *same* structure, so I was hoping for a simpler solution using just plpgsql. Any ideas? Thanks __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Linked Lists example.
Hi, Can anyone give me a real world example of where a 'linked list' would be used in PostgreSQL or SQL in general. Just been reading up a little on them and am wondering how/when to use them. Many thanks Rudi. ---(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
[SQL]
Re: [SQL] A generic trigger?
Peter Childs <[EMAIL PROTECTED]> writes:
> Yes it is possible and I've done it. The reason I'm not using it is because I
> wrote it in Pl/Python and if you attach the same trigger to more than one
> table in the same transaction pg/python (actually the entire server crashes
> but thats not the point) crashes. Well it did when I last tested it in early
> versions.
I've been expecting someone to submit a fix for this, but nobody did
:-(. So I went ahead and repaired it in CVS tip. The patch is attached
if you want to try patching your local copy (it looks like it will apply
to 7.3 branch with some fuzz, but I have not actually tested it there).
regards, tom lane
*** src/pl/plpython/plpython.c.orig Mon Aug 4 14:40:50 2003
--- src/pl/plpython/plpython.c Sun Sep 14 13:07:02 2003
***
*** 224,236
static PyObject *PLy_procedure_call(PLyProcedure *, char *, PyObject *);
! /* returns a cached PLyProcedure, or creates, stores and returns
! * a new PLyProcedure.
! */
! static PLyProcedure *PLy_procedure_get(FunctionCallInfo fcinfo, bool);
static PLyProcedure *PLy_procedure_create(FunctionCallInfo fcinfo,
!bool is_trigger,
HeapTuple procTup, char *key);
static void PLy_procedure_compile(PLyProcedure *, const char *);
--- 224,234
static PyObject *PLy_procedure_call(PLyProcedure *, char *, PyObject *);
! static PLyProcedure *PLy_procedure_get(FunctionCallInfo fcinfo,
! Oid
tgreloid);
static PLyProcedure *PLy_procedure_create(FunctionCallInfo fcinfo,
!Oid tgreloid,
HeapTuple procTup, char *key);
static void PLy_procedure_compile(PLyProcedure *, const char *);
***
*** 326,332
{
DECLARE_EXC();
Datum retval;
- volatile bool is_trigger;
PLyProcedure *volatile proc = NULL;
enter();
--- 324,329
***
*** 337,343
elog(ERROR, "could not connect to SPI manager");
CALL_LEVEL_INC();
- is_trigger = CALLED_AS_TRIGGER(fcinfo);
SAVE_EXC();
if (TRAP_EXC())
--- 334,339
***
*** 364,379
* PLy_restart_in_progress);
*/
! proc = PLy_procedure_get(fcinfo, is_trigger);
!
! if (is_trigger)
{
! HeapTuple trv = PLy_trigger_handler(fcinfo, proc);
retval = PointerGetDatum(trv);
}
else
retval = PLy_function_handler(fcinfo, proc);
CALL_LEVEL_DEC();
RESTORE_EXC();
--- 360,380
* PLy_restart_in_progress);
*/
! if (CALLED_AS_TRIGGER(fcinfo))
{
! TriggerData *tdata = (TriggerData *) fcinfo->context;
! HeapTuple trv;
+ proc = PLy_procedure_get(fcinfo,
+
RelationGetRelid(tdata->tg_relation));
+ trv = PLy_trigger_handler(fcinfo, proc);
retval = PointerGetDatum(trv);
}
else
+ {
+ proc = PLy_procedure_get(fcinfo, InvalidOid);
retval = PLy_function_handler(fcinfo, proc);
+ }
CALL_LEVEL_DEC();
RESTORE_EXC();
***
*** 962,971
}
! /* PLyProcedure functions
*/
static PLyProcedure *
! PLy_procedure_get(FunctionCallInfo fcinfo, bool is_trigger)
{
Oid fn_oid;
HeapTuple procTup;
--- 963,979
}
! /*
! * PLyProcedure functions
! */
!
! /* PLy_procedure_get: returns a cached PLyProcedure, or creates, stores and
! * returns a new PLyProcedure. fcinfo is the call info, tgreloid is the
! * relation OID when calling a trigger, or InvalidOid (zero) for ordinary
! * function calls.
*/
static PLyProcedure *
! PLy_procedure_get(FunctionCallInfo fcinfo, Oid tgreloid)
{
Oid fn_oid;
HeapTuple procTup;
***
*** 983,991
if (!HeapTupleIsValid(procTup))
elog(ERROR, "cache lookup failed for function %u", fn_oid);
! rv = snprintf(key, sizeof(key), "%u%s",
! fn_oid,
! is_trigger ? "_trigger" : "");
if ((rv >= sizeof(key)) || (rv < 0))
elog(ERROR, "key too long");
--- 991,997
if (!HeapTupleIsValid(procTup))
elog(ERROR, "cache lookup failed for function %u", fn_oid);
! rv = snprintf(key, sizeof(key), "%u_%u", fn_oid, tgreloid);
if ((rv >= sizeof(key)) || (rv < 0))
elog(ERROR, "key too long");
***
*** 1012,1018
}
if (proc == NULL)
!
[SQL] change a field
Dear, I want to change a field of a record after the modification of another field of the same record or during an insert of a new record. Suppose the following table field | type -- id | serial sign| integer value | integer real_value | integer When inserting a new record, giving values to sign and value, the field real_value should be the product of the fields sign and value. When updating an existing record, the value of the field real_value should be recalculated. I tried with plpgsql functions and triggers but with no success. It works only when inserting new records and not when updating a field. What is the best way to solve this problem ? Kind regards Patrick Meylemans ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Plz, what is the most "correct" method
Hi, Given 2 tables: Table1: idAuto,int,PrimKey table2idint txt nvarchar50 Table2: idAuto,int,PrimKey order int The scenario: Table2.order defines how the table1.txt is should be ordered. Table1.table2id contains the id of the order. This cannot be changed How do I select all Table1.txt values but ordered by their corresponding values of the table2.order field? -- Thx, PipHans --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.516 / Virus Database: 313 - Release Date: 01-09-2003 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Linked Lists example.
Jesse, Thanks - that's a good example. Could you also have built you CMS solution without linked lists ? I'm curious to see if the rule 'there's more than one way to the top of the mountain' applies here as it usualy does. Cheers Rudi. Jesse Scott wrote: In my current CMS project, I use a linked list concept. I've never read about linked-lists specifically for SQL, so I don't know if there are any nuances I missed by applying a basic CS approach to it. Anyway, here are simplified versions of a few of my tables: content -- cidserial title varchar(255) first_element int4 (FK -> content_elements.element_id) content_elements -- element_id serial type varchar(64) (FK -> element_types.element_type_id) next_element int4 (FK -> content_element.element_id) Now, in content_elements I also have a few fields that are foreign keys to the various tables that actually hold the different element types such as paragraphs, images, etc... I should really write some custom triggers to really ensure things end up linked correctly. But so far it has been working well. Whenever content is "published" I walk the linked list and build static HTML out of the article so that displaying content isn't very database intensive. There have been a couple times when I've wished I had made it a doubly-linked list, so I'll probably add that in the next version, but that's just a convenience thing basically. Makes it easier to move elements around and delete them etc... Let me know if I didn't explain this very well, I'd be happy to send you the actual database specs and some sample code. -Jesse Rudi Starcevic wrote: Hi, Can anyone give me a real world example of where a 'linked list' would be used in PostgreSQL or SQL in general. Just been reading up a little on them and am wondering how/when to use them. Many thanks Rudi. ---(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 ---(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] change a field
Patrick Meylemans writes: > What is the best way to solve this problem ? Show us your code. -- Peter Eisentraut [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])
Re: [SQL] Linked Lists example.
Jesse, >> This approach probably wouldn't work out very well if you needed to pull the content out of the database on every view because there are >> lots of queries and loops involved in following the list. Yes I see. As usual with web development the 'flow of information' dictates alot in which solution to use. I like your 'publish to static' concept. It sure does speed things up for the end user and lower's system resource uses as the pages are not built with every view. I'll keep an eye on http://www.phpblaze.org/ and look forward to checking out your initial version. Cheers. Jesse Scott wrote: Obviously there are tons of ways to create a CMS (just look at how many there are out there!) and I've actually created a couple different solutions myself. The reason I took the approach I did with this one is that it gives you a lot of flexibility with adding metadata to different elements and reusing those elements it multiple pieces of content. This approach probably wouldn't work out very well if you needed to pull the content out of the database on every view because there are lots of queries and loops involved in following the list. With the "publish to static" approach I'm taking with this, pretty much every user-side page that I've done so far renders in under .04 seconds on our pretty pitiful development box. That's using Smarty templates (with Smarty caching turned off) and using the ionCube PHP Accelerator. The project this example is from is: http://www.phpblaze.org/ I'm not quite done with the initial version so I haven't finished the website yet. -Jesse Rudi Starcevic wrote: Jesse, Thanks - that's a good example. Could you also have built you CMS solution without linked lists ? I'm curious to see if the rule 'there's more than one way to the top of the mountain' applies here as it usualy does. Cheers Rudi. Jesse Scott wrote: In my current CMS project, I use a linked list concept. I've never read about linked-lists specifically for SQL, so I don't know if there are any nuances I missed by applying a basic CS approach to it. Anyway, here are simplified versions of a few of my tables: content -- cidserial title varchar(255) first_element int4 (FK -> content_elements.element_id) content_elements -- element_id serial type varchar(64) (FK -> element_types.element_type_id) next_element int4 (FK -> content_element.element_id) Now, in content_elements I also have a few fields that are foreign keys to the various tables that actually hold the different element types such as paragraphs, images, etc... I should really write some custom triggers to really ensure things end up linked correctly. But so far it has been working well. Whenever content is "published" I walk the linked list and build static HTML out of the article so that displaying content isn't very database intensive. There have been a couple times when I've wished I had made it a doubly-linked list, so I'll probably add that in the next version, but that's just a convenience thing basically. Makes it easier to move elements around and delete them etc... Let me know if I didn't explain this very well, I'd be happy to send you the actual database specs and some sample code. -Jesse Rudi Starcevic wrote: Hi, Can anyone give me a real world example of where a 'linked list' would be used in PostgreSQL or SQL in general. Just been reading up a little on them and am wondering how/when to use them. Many thanks Rudi. ---(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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] change a field
> I want to change a field of a record after the modification of another > field of the same record or during an insert of a new record. You can easily accomplish this in a BEFORE trigger. It is applicable for both insert & update. regards, bhuvaneswaran ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] MD5() function not available ??
Hey, I've searched for MD5 crypting function in PG, but I did not find it. Anyone knows how to implement this function in PG ? Best wishes, Marek L. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
