[SQL] MD5 function is 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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] [GENERAL] MD5 function is not available ?
On Thursday 11 September 2003 09:38, Marek Lewczuk wrote: > 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 ? Is it not in the contrib/pgcrypto directory in the source distro? Or, check the contrib package of your binary distro. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] MD5 function is 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. Use contrib/pgcrypto Regards, Tomasz Myrta ---(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
[SQL] How do I replace select ... into commands?
I have a number of sybase procedures in which I use something like declare mx : integer; begin select max(field) from table into mx; return mx; end; Postgresql is telling me that select...into has not been implemented yet. what is the best way to perform this action with the commands offered in plpgsql? much thanks for the earlier help. Sincerely Richard Sydney-Smith
Re: [SQL] How do I replace select ... into commands?
On Fri, 12 Sep 2003, Richard Sydney-Smith wrote: > I have a number of sybase procedures in which I use something like > > declare > mx : integer; > begin > select max(field) from table into mx; > return mx; > end; > > Postgresql is telling me that select...into has not been implemented > yet. what is the best way to perform this action with the commands > offered in plpgsql? Something like the above should work, what's the exact function and error message and what version are you using? ---(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] How do I replace select ... into commands?
Stephen replied :
> Something like the above should work, what's the exact function and error
> message and what version are you using?
>
>
thanks Stephen.
Exact function definition follows:
PG Version is 7.3.1 on Windows 2000
This procedure allocates unique record number to a number of tables. In the
application it is important that I know the records ID number before it is
inserted into the database. Also use to assign batch and session numbers
etc.
CREATE FUNCTION public.make_rsn(bpchar, bpchar, int4) RETURNS int4 AS '
declare
tbl alias for $1;
seq_fld alias for $2;
incr alias for $3;
rsn integer := 0;
lastrsn integer := 0;
mx integer := 0;
begin
-- look for existing last RSN
select "max"(seq_val) as m into lastrsn from fseqkeys where seq_key = tbl;
if lastrsn=0 or lastrsn is null then
-- no pre existing RSN so we have to search the table
if tbl=''BATCH'' or position(''-'' in tbl)>0 then
mx := 100;
else
execute ''select max('' || seq_fld || '') into mx from '' || tbl;
end if;
rsn := mx+1;
-- dont allow rsn < 100
if rsn<100 or rsn is null then
rsn := 100;
end if;
lastrsn := rsn;
-- record the new rsn
insert into fseqkeys (seq_key,seq_val) values (tbl,rsn);
end if;
-- reserve the required number of rows
rsn := lastrsn+incr;
--and update the fseqkeys table with the RSN number/s we have just used
update fseqkeys set seq_val = rsn where seq_key=tbl;
-- now return the RSN number to the user
return rsn;
end;
' LANGUAGE 'plpgsql' VOLATILE;
- Original Message -
From: "Stephan Szabo" <[EMAIL PROTECTED]>
To: "Richard Sydney-Smith" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Friday, September 12, 2003 10:46 AM
Subject: Re: [SQL] How do I replace select ... into commands?
> On Fri, 12 Sep 2003, Richard Sydney-Smith wrote:
>
> > I have a number of sybase procedures in which I use something like
> >
> > declare
> > mx : integer;
> > begin
> > select max(field) from table into mx;
> > return mx;
> > end;
> >
> > Postgresql is telling me that select...into has not been implemented
> > yet. what is the best way to perform this action with the commands
> > offered in plpgsql?
>
> Something like the above should work, what's the exact function and error
> message and what version are you using?
>
>
>
---(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] How do I replace select ... into commands?
"Richard Sydney-Smith" <[EMAIL PROTECTED]> writes:
> execute ''select max('' || seq_fld || '') into mx from '' || tbl;
You can't use INTO in an EXECUTE'd select (basically because the string
to be executed is not processed by plpgsql at all, merely sent down to
the SQL engine, which does not know the output variable mx). The way to
get results out of an executed select is to use FOR ... IN EXECUTE.
Which is a kluge, but it holds the fort until someone gets around to
redesigning this code. See the manual.
regards, tom lane
---(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] How do I replace select ... into commands?
Thanks that fixed it.
the more I use it the happier I get with postgresql.
Best Regards
Richard
- Original Message -
From: "Tom Lane" <[EMAIL PROTECTED]>
To: "Richard Sydney-Smith" <[EMAIL PROTECTED]>
Cc: "Stephan Szabo" <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
Sent: Friday, September 12, 2003 11:20 AM
Subject: Re: [SQL] How do I replace select ... into commands?
> "Richard Sydney-Smith" <[EMAIL PROTECTED]> writes:
> > execute ''select max('' || seq_fld || '') into mx from '' || tbl;
>
> You can't use INTO in an EXECUTE'd select (basically because the string
> to be executed is not processed by plpgsql at all, merely sent down to
> the SQL engine, which does not know the output variable mx). The way to
> get results out of an executed select is to use FOR ... IN EXECUTE.
> Which is a kluge, but it holds the fort until someone gets around to
> redesigning this code. See the manual.
>
> regards, tom lane
>
> ---(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
>
>
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] how to call a function with row-type arg
hi how to call a function with a row_type arg ?? that is the question CREATE FUNCTION foo(tablename) returns int . SELECT foo ( ??? ); ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
