[SQL] trigger : emulate "instead of" with before ?
Hello, I need a trigger which is updating a delete flag of a row instead of deleting it physically. How do I implement a trigger which doesn't execute a delete without raising an exception in plsql ? CREATE TRIGGER trigger1 BEFORE DELETE ON table1 FOR EACH ROW EXECUTE PROCEDURE setDeleteFlag (); If I use RAISE EXCEPTION "..." the delete statement isn't executed, but I think the transaction is rolled back too, am I right ? So what is the best way to implement something (Oracle like) like that : CREATE TRIGGER trigger1 INSTEAD OF DELETE ON table1 FOR EACH ROW EXECUTE PROCEDURE setDeleteFlag (); thx ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] trigger : emulate "instead of" with before ?
I'm not a postgres expert, and I certainly don't know the details of your situation, but it seems to me you may want to use a rule instead of a trigger. Then, you can intercept the delete query and simply re-write it to be an update query that sets your deleted flag. -- Matthew Nuzum www.bearfruit.org [EMAIL PROTECTED] > -Original Message- > From: [EMAIL PROTECTED] [mailto:pgsql-sql- > [EMAIL PROTECTED] On Behalf Of Albrecht Berger > Sent: Friday, June 13, 2003 7:51 AM > To: pgsql > Subject: [SQL] trigger : emulate "instead of" with before ? > > Hello, > I need a trigger which is updating a delete flag of a row instead of > deleting it physically. > > How do I implement a trigger which doesn't execute a delete without > raising > an exception > in plsql ? > > CREATE TRIGGER trigger1 BEFORE DELETE ON table1 > FOR EACH ROW EXECUTE PROCEDURE setDeleteFlag (); > > If I use > RAISE EXCEPTION "..." > the delete statement isn't executed, but I think the transaction is rolled > back too, am I right ? > > So what is the best way to implement something (Oracle like) like that : > CREATE TRIGGER trigger1 INSTEAD OF DELETE ON table1 > FOR EACH ROW EXECUTE PROCEDURE setDeleteFlag (); > > thx > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] trigger : emulate "instead of" with before ?
I believe that if you return NULL from the trigger function, the delete will not actually be done. You could set the value of the delete flag by reference to OLD.fieldname. I have not actually done this myself, so caveat emptor. --- Albrecht Berger <[EMAIL PROTECTED]> wrote: > Hello, > I need a trigger which is updating a delete flag of > a row instead of > deleting it physically. > > How do I implement a trigger which doesn't execute a > delete without raising > an exception > in plsql ? > > CREATE TRIGGER trigger1 BEFORE DELETE ON table1 > FOR EACH ROW EXECUTE PROCEDURE setDeleteFlag (); > > If I use > RAISE EXCEPTION "..." > the delete statement isn't executed, but I think the > transaction is rolled > back too, am I right ? > > So what is the best way to implement something > (Oracle like) like that : > CREATE TRIGGER trigger1 INSTEAD OF DELETE ON table1 > FOR EACH ROW EXECUTE PROCEDURE setDeleteFlag (); > > thx > > > ---(end of > broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] trigger : emulate "instead of" with before ?
"Albrecht Berger" <[EMAIL PROTECTED]> writes: > I need a trigger which is updating a delete flag of a row instead of > deleting it physically. > How do I implement a trigger which doesn't execute a delete without raising > an exception in plsql ? If the BEFORE DELETE trigger returns NULL, the delete is skipped. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] create tables within functions
hello, Can anyone help me create a function that creates a table, in this way for example : create function create_table(text) returns integer as ' begin create table $1 (id integer, stuff text); return 0; end;' language plpgsql; it does not work !! thnx __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] create tables within functions
On Fri, 13 Jun 2003, Demidem Mohamed Amine wrote: > hello, > > Can anyone help me create a function that creates a > table, in this way for example : See EXECUTE for a way to execute a query that you've built into a string, for example, something like: EXECUTE ''create table '' || $1 || '' (id integer, stuff text)''; > > create function create_table(text) returns integer as > ' > begin > create table $1 (id integer, stuff text); > return 0; > end;' > language plpgsql; > > it does not work !! > > thnx > > __ > Do you Yahoo!? > Yahoo! Calendar - Free online calendar with sync to Outlook(TM). > http://calendar.yahoo.com > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] question on rules
Tom Lane wrote: [EMAIL PROTECTED] writes: i'd like to write an rule which fills out some empty attrs on insert (w/ data from other given attrs). You'd be better off doing this with a BEFORE INSERT trigger. The only way to do it with rules would be to create a view over the basetable, create an insert rule over the view and then have the application doing the insert into the view instead. So "better" could be considered an understatement :-) Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] create tables within functions
--- Demidem Mohamed Amine <[EMAIL PROTECTED]> wrote: > hello, > > Can anyone help me create a function that creates a table: create function create_table( text ) returns integer as ' declare p_tab alias for $1; v_exec text; begin v_exec := ''create table '' || p_tab || ''( id integer )''; execute v_exec; end; ' language 'plpgsql'; __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com ---(end of broadcast)--- TIP 9: most folks find a random_page_cost between 1 or 2 is ideal
[SQL] rpm scripts
Hello,
I am building an rpm for Mitle SME (a scaled down redhat 7.3) and
have all functions working except:
I need to have the rpm when installed create a database and a
user with privilege to that database. These commands are easy
enough in general...
# service postgresql start
# su postgres
# createdb account
# psql account < Pg-tables.sql
# psql account < US_General-chart.sql
# exit
But, I need these functions to be executed when one installs my rpm.
For Mysql, I have the following in the %post:
---snippet-from-spec-
%post
# This section creates the database, dbuser, dbpasswd and data after the
# package has been installed
pw=`/bin/cat /etc/openldap/ldap.pw`
/bin/echo exit | /usr/bin/mysql --password=$pw catalog 2>&1 &> /dev/null
if [ "$?" = "1" ] ; then
/bin/echo "Creating catalog database..."
/usr/bin/mysqladmin --password=$pw create catalog
/bin/echo "grant all on catalog.* to [EMAIL PROTECTED] identified by 'shopuser';"
| /usr/bin/mysql --password=$pw
/usr/bin/mysql --password=$pw catalog <
/home/e-smith/files/primary/html/store/catalog/install/oscommerce.sql
/usr/bin/mysqladmin --password=$pw reload
fi
--end-of-snippet-from-spec---
This creates a database using the root pasword (Mitel SME keeps
this in ldap.pw (which is probably not needed for postgresql).
What commands would I use to complete similar needs for
a postgresql database to be created?
Thank you for the help.
--
Best regards,
Craig Jensenmailto:[EMAIL PROTECTED]
Ace Net-Tech
http://www.acenet-tech.org/ My computer services site.
http://www.acenet-tech.org/phpBB2/ My forum, business and otherwise.
}}}All ouotgoing messages scanned and verified virus-free by Norton Antivirus
2003.{{{
---(end of broadcast)---
TIP 9: most folks find a random_page_cost between 1 or 2 is ideal
Re: [SQL] subselects - which is faster?
On Thu, 12 Jun 2003, Cedar Cox wrote: > It's been a while since I've done much SQL.. > > . I'm wondering which of these two queries is faster (both get the > same result)? > > . Which one is more correct? Does it even matter or are they the > same? The first one reads easier to me. > > . What's the difference between "InitPlan" and "SubPlan"? > > explain SELECT eqid, >(select name from tbleqattrtypes where id= > (select eqattrtypeid from tbleqattrs > where id=main.eqattrid)) > as attrtype, eqattrid from tbleqattrmap as main; > > NOTICE: QUERY PLAN: > Seq Scan on tbleqattrmap main (cost=0.00..1.15 rows=15 width=8) > SubPlan > -> Seq Scan on tbleqattrtypes (cost=0.00..1.04 rows=1 width=12) > InitPlan > -> Seq Scan on tbleqattrs (cost=0.00..1.09 rows=1 width=4) > > > explain SELECT eqid, >(select > (select name from tbleqattrtypes where id=sec.eqattrtypeid) >from tbleqattrs as sec where id=main.eqattrid) > as attrtype, eqattrid from tbleqattrmap as main; > > NOTICE: QUERY PLAN: > Seq Scan on tbleqattrmap main (cost=0.00..1.15 rows=15 width=8) > SubPlan > -> Seq Scan on tbleqattrs sec (cost=0.00..1.09 rows=1 width=4) > SubPlan > -> Seq Scan on tbleqattrtypes (cost=0.00..1.04 rows=1 width=12) > > One additional detail: right now the tables are all very small, and > tbleqattrtypes will not grow much, but tbleqattrs will eventually be > very large. A couple of quick points. 1: Postgresql uses a cost based planner, not a rule based planner. This means you need to run analyze every so often to let the database know how many rows of what kind of data are in each table. This also means that if you are going to have 100,000 rows when you go live, then you need to create 100,000 representative rows now in order to figure out which is faster. 2: User 'explain analyze select ...' to make the database actually run the query and time it for you. Then you'll know which is faster. ---(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] How to make a IN without a table... ?
On Thu, 12 Jun 2003, David Pradier wrote: > On Thu, Jun 12, 2003 at 01:16:27PM +0200, Christoph Haller wrote: > > > > > > i ran today in a problem when doing some (i mean too much for me) > > advanced sql... > > > > > > What i want to do is something like this: > > > > > > SELECT > > > my_var1, > > > my_var2, > > > my_function(my_var1, my_var2) > > > FROM ( > > > SELECT > > > '1', > > > '2', > > > '3', > > > '4' > > > ) AS my_var1_values, > > > ( > > > SELECT > > > '1', > > > '2', > > > '3', > > > '4' > > > ) AS my_var2_values > > > > > > In short, i want to calculate the result of the function my_function > > for > > > some values of my_var1, cross by some values of my_var2. > > > These values are not taken in a table, but put in directly. > > > They are a lot, so i would prefer not to write the whole thing, line > > > after line. (Let's say 10 values for the first, and 40 for the second > > => > > > 400 lines of code to maintain...) > > > > > > I really don't see how to do this :-/ > > > > > What about using a TEMP TABLE? > > I avoid as hell to use temporary tables. This is part of a complex > database, with more than 250 different tables. So i don't even want to > think about adding temporary tables, brrr ! Just FYI, temporary tables in pgsql are invisible to other connections even if they have the same name, and are auto-dropped when the connection is dropped. ---(end of broadcast)--- TIP 8: explain analyze is your friend
