RE: Using SELECT as DDL/DML statement is wrong (was RE: [SQL] reinitialize a sequence?)
Title: RE: Using SELECT as DDL/DML statement is wrong (was RE: [SQL] reinitialize a sequence?)
In fact, I would have thought that this could be done using
ALTER SEQUENCE sequence_name SET property = value
But, altering the database in a procedure called from a select is a design decision, and if somebody wants to do it, well, it's their problem. There may (on very few occasions, one would hope) actually be some good reasons to do this.
Cheers...
MikeA
-Original Message-
From: Edmar Wiggers [mailto:[EMAIL PROTECTED]]
Sent: 05 December 2000 16:50
To: [EMAIL PROTECTED]
Subject: Using SELECT as DDL/DML statement is wrong (was RE: [SQL]
reinitialize a sequence?)
If and when stored procedures are supported, there should be some way to
prevent functions called in a SELECT statement to modify the database
(create, insert, etc.).
It is confusing (and wrong IMHO) to use statements like
SELECT setval('tablename_serfield_seq',max(serfield)) FROM tablename;
(which is used to reset a sequence)
That should be done with
EXECUTE procedure(tablename_name,sequence_name);
(not sure if execute is the right keyword)
Yours sincerely,
Edmar Wiggers
BRASMAP Information Systems
+55 48 9960 2752
**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
www.mimesweeper.com
**
[SQL] what's wrong with my plperl function?
Hello,
i am still at the same stage thanks to Oliver Elphick the plperl
module is working, as a stand alone per program the thing works (sole
difference, i use $toparse = shift; instead of the $toparse = $_[0];
in the sql-function
here's the thing:
CREATE FUNCTION cleanFromSep(text,text) RETURNS int4
AS '
$toparse = $_[0];
$work = $_[0];
$format = $_[1];
print "first step $work\n";
$work =~s/\.//g;
print "second step $work\n";
$work =~s/\,//g;
print "final step $work\n";
$work *= 100
if($format =~/,\d{2}$/ && !($toparse=~/,\d{2}$/));
print "eventual mult step $work\n";
return $work;
' LANGUAGE 'plperl';
the perl script gives:
./test.pl 1.000.000 999.999.999,99
first step 1.000.000
second step 100
final step 100
eventual mult step 1
and the SQL function:
select cleanFromSep('1.000.000','999.999.999,99');
cleanfromsep
--
0
(1 row)
so what am i doing wrong? besides how can i output debug stuff from the
perl script? print manifestingly doesn't work...
--
ciao bboett
==
[EMAIL PROTECTED]
http://inforezo.u-strasbg.fr/~bboett http://erm1.u-strasbg.fr/~bboett
===
the total amount of intelligence on earth is constant.
human population is growing
RE: Using SELECT as DDL/DML statement is wrong (was RE: [SQL] reinitialize a sequence?)
> But, altering the database in a procedure > called from a select is a design decision, > and if somebody wants to do it, well, it's > their problem. There may (on very few > occasions, one would hope) actually be some > good reasons to do this. Ok, it's their problem. But, as a DBA, I would like to have stored procedures. Having that, I would also like a way to prevent any application development that uses SELECT statements to alter the database. By definition, that's not what SELECT is for (and so it is bad practice to use it that way). Currently however, that's the only whay to do it (use something similar to stored procs). And, true, that's much better than not doing it at all. Yours sincerely, Edmar Wiggers BRASMAP Information Systems +55 48 9960 2752
Re: [SQL] what's wrong with my plperl function?
Bruno Boettcher <[EMAIL PROTECTED]> writes: > so what am i doing wrong? I'm confused too... > besides how can i output debug stuff from the > perl script? print manifestingly doesn't work... I don't see why print wouldn't work. Realize however that it will go to the postmaster's stdout, so you'd better not have started the postmaster with -S, and you should have redirected the postmaster's stdout/stderr into a log file someplace. You might have better luck printing to stderr, BTW ... that should get flushed to the disk file once per line, whereas Perl might think it only needs to flush stdout once per block. Also, it looks like plperl supports elog(NOTICE, "text") and elog(ERROR, "text"), though I haven't tried 'em. regards, tom lane
Re: [SQL] what's wrong with my plperl function?
On Wed, Dec 06, 2000 at 10:54:58AM -0500, Tom Lane wrote: > I'm confused too... :D > I don't see why print wouldn't work. Realize however that it will > go to the postmaster's stdout, so you'd better not have started the yep found it in the log > Also, it looks like plperl supports elog(NOTICE, "text") and > elog(ERROR, "text"), though I haven't tried 'em. that does it! but another strange thing: first step 1.000.000 second step final step code was print "first step $work\n"; $work =~s/\.//g; print "second step $work\n"; $work =~s/\,//g; print "final step $work\n"; seems the =~ isn't supported. ? :( tryed even this: $_ = $work; s/\.//g; $work =$_; but same this is basic perl at least i thought so... -- ciao bboett == [EMAIL PROTECTED] http://inforezo.u-strasbg.fr/~bboett http://erm1.u-strasbg.fr/~bboett === the total amount of intelligence on earth is constant. human population is growing
Re: [SQL] what's wrong with my plperl function?
On Wed, Dec 06, 2000 at 05:52:13PM +0100, Bruno Boettcher wrote: > $work =~s/\.//g; ehm yeah stupid me... think i even read it somewhere in the docu should be $work =~ s/\\.//g; :( at least it works now... -- ciao bboett == [EMAIL PROTECTED] http://inforezo.u-strasbg.fr/~bboett http://erm1.u-strasbg.fr/~bboett === the total amount of intelligence on earth is constant. human population is growing
[SQL] CallableStatement
Hi Thomas,
thanks for taking the time to respond. If I try to
use a CallableStatement object in Java using Posgresql driver included
in PostgreSQL 7.0.2. I get an SQLException which includes the message;
"Callable Statements are not supported at this time. "
snippet of code
String sql="";
try {
conn = ConnectionPoolManager.getConnection();
CallableStatement stmt = conn.prepareCall(sql);
stmt.execute();
catch (SQLException e) {
..
}
- Original Message -
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, December 06, 2000 3:10 PM
Subject: re : [SQL] CallableStatement
:
: what's your problem exactly ?
:
: Thomas,
:
:
:
: ==
: Posté par Shane McEneaney le 6/12.
:
: Hi,
: can anybody tell me where I can get a free JDBC driver for
: Postgresql that implements CallableStatement? The driver in the
: distribution I have is PostgreSQL 7.0.2.
:
: Thanks in advance,
:
: Shane
:
Re: [SQL] subselects
hubert depesz lubaczewski wrote: > > On Wed, Nov 29, 2000 at 07:03:36PM -0500, Joseph Shraibman wrote: > > I tried to do this: > > SELECT r , a , (SELECT u , re FROM dir WHERE u = a) , cdate FROM rep > > WHERE m IN(190); > > why dont you use simple join? > like: > select rep.r, rep.a, rep.cdate, dir.u, dir.re from rep, dir where rep.m in > (190) and rep.a = dir.u; > > this should (i guess) work perfectly > Because in my case I'm dynamically generated the select by passing in some String values, which are inserted into SELECT x,x,x from mytable so I can't do a simple join. -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com
[SQL] CallableStatement
Hi, can anybody tell me where I can get a free JDBC driver for Postgresql that implements CallableStatement? The driver in the distribution I have is PostgreSQL 7.0.2. Thanks in advance, Shane
