Re: [SQL] Referencing external table in update/insert triggers
On 17/02/10 15:18, Amitabh Kant wrote: CREATE OR REPLACE FUNCTION update_data() RETURNS TRIGGER AS $update_data$ BEGIN IF NEW.t1f4> t2.t2f4 UPDATE t2 set t2f2=NEW.t1f2, t2f3=NEW.t1f3, t2f4=NEW.t1f4 where t2f1=NEW.d1; RETURN NEW; END IF; END; $update_data$ LANGUAGE plpgsql; I would like to compare the date present in the t2f4 with the new data being updated through the trigger. Using the if line as listed above returns an error. You can always do something like: SELECT t2.t2f4 INTO my_variable FROM t2 WHERE ... IF NEW.t1f4 > my_variable However, for this case you can just do an update with an extended where clause: UPDATE t2 ... WHERE t2f1=NEW.d1 AND NEW.t1f4 > t2.t2f4 No need for the IF. -- Richard Huxton Archonet Ltd -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Referencing external table in update/insert triggers
On Thu, Feb 18, 2010 at 2:53 PM, Richard Huxton wrote: > On 17/02/10 15:18, Amitabh Kant wrote: > >> >> CREATE OR REPLACE FUNCTION update_data() RETURNS TRIGGER AS $update_data$ >> BEGIN >> IF NEW.t1f4> t2.t2f4 >> UPDATE t2 set t2f2=NEW.t1f2, t2f3=NEW.t1f3, t2f4=NEW.t1f4 where >> t2f1=NEW.d1; >> RETURN NEW; >> END IF; >> END; >> $update_data$ LANGUAGE plpgsql; >> > > I would like to compare the date present in the t2f4 with the new data >> being >> updated through the trigger. Using the if line as listed above returns an >> error. >> > > You can always do something like: > SELECT t2.t2f4 INTO my_variable FROM t2 WHERE ... > IF NEW.t1f4 > my_variable > > However, for this case you can just do an update with an extended where > clause: > UPDATE t2 ... WHERE t2f1=NEW.d1 AND NEW.t1f4 > t2.t2f4 > > No need for the IF. > > -- > Richard Huxton > Archonet Ltd > Thanks Richard. With regards Amitabh Kant
[SQL] Problem with batch insert
Hi,
First I'm sorry if this message shouldn't be posted in this mailing list but I
wasn't sure if it should be here or in jdbc mailing list.
I have a weird problem with batch insert. It works fine on my development
machine but when I upload to the server it fails. Here is the log:
DEBUG [http-8443-2] - {conn-100123} Preparing Statement: insert into
target_filters_sections (filter_id, section_id) values (?, ?) , (?, ?)
DEBUG [http-8443-2] - {pstm-100124} Executing Statement: insert into
target_filters_sections (filter_id, section_id) values (?, ?) , (?, ?)
DEBUG [http-8443-2] - {pstm-100124} Parameters: [23, 229, 23, 141]
DEBUG [http-8443-2] - {pstm-100124} Types: [java.lang.Integer,
java.lang.Integer, java.lang.Integer, java.lang.Integer]
DEBUG [http-8443-2] - Unable to translate SQLException with SQL state '42601',
error code '0, will now try the fallback translator
DEBUG [http-8443-2] - Returning JDBC Connection to DataSource
org.springframework.jdbc.BadSqlGrammarException: SqlMapClient operation; bad
SQL grammar []; nested exception is
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred while applying a parameter map.
--- Check the addSectionsToTargetsFilter-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: org.postgresql.util.PSQLException: ERROR: syntax error at or near ","
Position: 113
And here is the log for the same operation on my development machine:
DEBUG [http-8080-1] - {conn-16} Preparing Statement: insert into
target_filters_sections (filter_id, section_id) values (?, ?) , (?, ?) , (?, ?)
DEBUG [http-8080-1] - {pstm-17} Executing Statement: insert into
target_filters_sections (filter_id, section_id) values (?, ?) , (?, ?) , (?, ?)
DEBUG [http-8080-1] - {pstm-17} Parameters: [28, 1603, 28, 1605, 28, 1604]
DEBUG [http-8080-1] - {pstm-17} Types: [java.lang.Integer,
java.lang.Integer, java.lang.Integer, java.lang.Integer, java.lang.Integer,
java.lang.Integer]
DEBUG [http-8080-1] - Returning JDBC Connection to DataSource
Postgre version on the server 8.1 on development 8.3
Any clues?
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problem with batch insert
zkn writes:
> I have a weird problem with batch insert. It works fine on my development
> machine but when I upload to the server it fails. Here is the log:
> DEBUG [http-8443-2] - {conn-100123} Preparing Statement: insert into
> target_filters_sections (filter_id, section_id) values (?, ?) , (?, ?)
> DEBUG [http-8443-2] - Unable to translate SQLException with SQL state
> '42601', error code '0, will now try the fallback translator
42601 is a syntax error, although I think you ought to complain to
somebody about the fact that your tools don't show you the actual server
error message.
> Postgre version on the server 8.1 on development 8.3
8.1 does not support multiple rows in a VALUES construct.
regards, tom lane
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problem with batch insert
On 18.02.2010, at 17:32, Tom Lane wrote:
> zkn writes:
>> I have a weird problem with batch insert. It works fine on my development
>> machine but when I upload to the server it fails. Here is the log:
>
>> DEBUG [http-8443-2] - {conn-100123} Preparing Statement: insert into
>> target_filters_sections (filter_id, section_id) values (?, ?) , (?, ?)
>
>> DEBUG [http-8443-2] - Unable to translate SQLException with SQL state
>> '42601', error code '0, will now try the fallback translator
>
> 42601 is a syntax error, although I think you ought to complain to
> somebody about the fact that your tools don't show you the actual server
> error message.
>
>> Postgre version on the server 8.1 on development 8.3
>
> 8.1 does not support multiple rows in a VALUES construct.
Yes, it was the postgre version.
>
> regards, tom lane
>
> --
> Sent via pgsql-sql mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
