[SQL] MD5 sums of large objects
Hello all together, I have a database containing lots of large objects. Now I'd like to compare large objects in my database and I thought of having a function which creates a hashsum (MD5, SHA-1 or whatever) of my large object, so I can use that in queries: create function lo_md5(id oid) returns text... Now I don't know if something like this is already included in the PostgreSQL distribution, could be found somewhere on pgfoundry or thirdly how to do it? If I have to program myself I would go for a C-language function which would use the libpq large-object functions to create the hashsums. -- ---> Dirk Jagdmann > http://cubic.org/~doj -> http://llg.cubic.org ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] MD5 sums of large objects
On Sun, Apr 08, 2007 at 07:03:17PM +0200, Dirk Jagdmann wrote: > I have a database containing lots of large objects. Now I'd like to > compare large objects in my database and I thought of having a > function which creates a hashsum (MD5, SHA-1 or whatever) of my large > object, so I can use that in queries: > > create function lo_md5(id oid) returns text... Something like this might work: create function lo_md5(id oid) returns text as $$ declare fdinteger; size integer; hashval text; INV_READ constant integer := 262144; SEEK_SET constant integer := 0; SEEK_END constant integer := 2; begin fd := lo_open(id, INV_READ); size := lo_lseek(0, 0, SEEK_END); perform lo_lseek(0, 0, SEEK_SET); hashval:= md5(loread(fd, size)); perform lo_close(fd); return hashval; end; $$ language plpgsql stable strict; For hash functions other than MD5 see contrib/pgcrypto. -- Michael Fuhr ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] Question on pgpsql function
Hi Everybody, I am using Postgres 8.1.0 and I have a requirement. I have a table create table weekly_tbl (id int, week_flag bit(7) not null default '111'); I want to create a function like this create function week_func (int) returns weekly_tbl as $$ select id, substr(week_flag,1,1) as monday_flag, substr(week_flag,2,1) as tuesday_flag, substr(week_flag,3,1) as wednesday_flag, substr(week_flag,4,1) as thursday_flag, substr(week_flag,5,1) as friday_flag, substr(week_flag,6,1) as saturday_flag, substr(week_flag,7,1) as sunday_flag from weekly_tbl where id=$1; $$ language SQL; I am getting an error message ERROR: function substr(bit, integer, integer) does not existHINT: No function matches the given name and argument types. You may need to add explicit type casts.CONTEXT: SQL function "week_func" I know I can do this in view. But for a purpose, I don't want to do it in view. Can somebody help me? Regards skarthi _ It’s tax season, make sure to follow these few simple tips http://articles.moneycentral.msn.com/Taxes/PreparationTips/PreparationTips.aspx?icid=WLMartagline
Re: [SQL] Question on pgpsql function
On Sunday 08 April 2007 12:47 pm, Karthikeyan Sundaram wrote: > Hi Everybody, > >I am using Postgres 8.1.0 and I have a requirement. > > I have a table > > create table weekly_tbl (id int, week_flag bit(7) not null default > '111'); > > I want to create a function like this > > create function week_func (int) returns weekly_tbl as > $$ > select id, >substr(week_flag,1,1) as monday_flag, >substr(week_flag,2,1) as tuesday_flag, >substr(week_flag,3,1) as wednesday_flag, >substr(week_flag,4,1) as thursday_flag, >substr(week_flag,5,1) as friday_flag, >substr(week_flag,6,1) as saturday_flag, >substr(week_flag,7,1) as sunday_flag > from weekly_tbl where id=$1; > $$ > language SQL; > > I am getting an error message > ERROR: function substr(bit, integer, integer) does not existHINT: No > function matches the given name and argument types. You may need to add > explicit type casts.CONTEXT: SQL function "week_func" > > I know I can do this in view. But for a purpose, I don't want to do it in > view. Can somebody help me? > > Regards > skarthi The problem is with your use of the substr() function. You are passing it a field of type bit (week_flag) where the function needs a text type. See the documentation for bit functions- http://www.postgresql.org/docs/8.2/interactive/functions-bitstring.html There are some bit compatible string functions mentioned. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] [ADMIN] Question on pgpsql function
Karthikeyan Sundaram <[EMAIL PROTECTED]> writes: > I am getting an error message > ERROR: function substr(bit, integer, integer) does not existHINT: No func= > tion matches the given name and argument types. You may need to add explici= > t type casts.CONTEXT: SQL function "week_func" I think you want substring() not substr(). regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] MD5 sums of large objects
Hello Michael, this works like charm. Although I did fix the argument for lo_lseek: CREATE OR REPLACE FUNCTION md5(id oid) RETURNS text as $$ DECLARE fdinteger; size integer; hashval text; INV_READ constant integer := 262144; -- 0x4 from libpq-fs.h SEEK_SET constant integer := 0; SEEK_END constant integer := 2; BEGIN IF id is null THEN RETURN NULL; END IF; fd := lo_open(id, INV_READ); size := lo_lseek(fd, 0, SEEK_END); PERFORM lo_lseek(fd, 0, SEEK_SET); hashval := md5(loread(fd, size)); PERFORM lo_close(fd); RETURN hashval; END; $$ language plpgsql stable strict; comment on FUNCTION md5(id oid) is 'Calculates the md5 sum of a large object.'; I vote for this function beeing included either somewhere in the contrib directories, as you often don't need the full power of pgcrypto is md5 suffices for your hashing needs. -- ---> Dirk Jagdmann > http://cubic.org/~doj -> http://llg.cubic.org ---(end of broadcast)--- TIP 1: 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] MD5 sums of large objects
On Mon, Apr 09, 2007 at 02:07:16AM +0200, Dirk Jagdmann wrote: > this works like charm. Although I did fix the argument for lo_lseek: Oops; thanks for fixing that. > INV_READ constant integer := 262144; -- 0x4 from libpq-fs.h You could also use a hex constant (the cast to integer is necessary): INV_READ constant integer := x'4'::integer; > IF id is null THEN >RETURN NULL; > END IF; The above check is unnecessary since the function is marked STRICT, aka RETURNS NULL ON NULL INPUT. > I vote for this function beeing included either somewhere in the > contrib directories, as you often don't need the full power of > pgcrypto is md5 suffices for your hashing needs. You could make a proposal in pgsql-hackers but I think 8.3 is in feature freeze so don't expect to see it until 8.4, if it's accepted at all. There's always PgFoundry :-) -- Michael Fuhr ---(end of broadcast)--- TIP 1: 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] Another question in functions
Hi team,
I have a requirement like this.
create table valid_lovs (code_id int not null,lov_value int not null
,description varchar(256),status bit(1) not null default '1',constraint lov_pk
primary key (code_id,lov_value));
insert into valid_lovs (code_id,lov_value,description) values (1,1,'1000
downloads');insert into valid_lovs (code_id,lov_value,description) values
(1,2,'2000 downloads');insert into valid_lovs (code_id,lov_value,description)
values (10,1,'US Dollar');insert into valid_lovs
(code_id,lov_value,description) values (10,2,'Singapore dollar');insert into
valid_lovs (code_id,lov_value,description) values (20,1,'Audio');insert into
valid_lovs (code_id,lov_value,description) values (20,2,'Video');insert into
valid_lovs (code_id,lov_value,description) values (20,3,'Overlay');
insert into valid_lovs (code_id, lov_value,description) values
(1000,1,'IMPRESSION_LOV');insert into valid_lovs (code_id,
lov_value,description) values (1000,10,'CURRENCY_LOV');insert into valid_lovs
(code_id, lov_value,description) values (1000,20,'MEDIA_FORMAT');
I need to write 2 functions.
1) Find_LOV. In this function I will pass only a text message but should return
an array.
create or replace function find_lov_func(in p_1 anyelement, out p_2 anyarray)
as$$ select array[x.code_id, x.lov_value] from valid_lovs x, valid_lovs y
where y.description = $1 and x.code_id = y.lov_value;$$language sql;
select find_lov_func('CURRENCY_LOV'::text);
I should get an output of
{10,1}
{10,2}
instead I am getting
ERROR: return type mismatch in function declared to return text[]DETAIL:
Actual return type is integer[].CONTEXT: SQL function "find_lov_func" during
startup
Q) How will I resolve this. I need to get array of integer only.
2) get_lov function: In this function, I will pass a text field and I should
get an integer and the text as output
for example
create or replace function get_lov_func(in p_1 varchar) returns setof
valid_lovs as$$ select x.lov_value, x.description from valid_lovs x,
valid_lovs y where y.description = $1 and x.code_id =
y.lov_value;$$language sql;
ERROR: return type mismatch in function declared to return valid_lovsDETAIL:
Final SELECT returns character varying instead of integer at column 2.CONTEXT:
SQL function "get_lov_func"
Can somebody help me in this?
Regards
skarthi
_
Take a break and play crossword puzzles - FREE!
http://games.msn.com/en/flexicon/default.htm?icid=flexicon_
wlmemailtaglinemarch07
[SQL] new idea
Let me know your opinion about next way of processing and extracting data. This would very comfortable for delivery xml-data into any program, for example into browser. Has this idea future ? What are you think ? 1. For exaple, you create SET: create table a ( id num primary key; data float; ); create table b ( id num primary key; ref num references a(id); data float; ); create table c ( id num primary key; link num references b(id); data float; ); insert into a values (1, 12.3); insert into b values (10, 1, 23.4); insert into b values (20, 1, 34.5); insert into b values (30, 1, 45.6); insert into c values (100,10,56.7); insert into c values (101,10,67.8); insert into c values (200,20,78.9); insert into c values (201,20,89.1); insert into c values (300,30,91.2); Request "a.b.c" (of Tree Manipulation Language :) ) will return the following (term "SET" means, that first table in request is parental table, next table is branch table) 2. For exaple, you create RELAY-RACE: create table a ( id num primary key; ref num references b(id); data float; ); create table b ( id num primary key; link num references c(id); data float; ); create table c ( id num primary key; data float; ); insert into с values (100,34.5); insert into b values (10, 100,23.4); insert into a values (1, 10, 12.3); Request "a.b.c" will return the following (term "RELAY-RACE" means, that first table in request is branch table, next table is parental table) --- Let's consider more complicated cases. 1. complicated case for "set" create table a ( id num primary key; data float; ); create table b ( id num primary key; ref1 num references a(id); ref2 num references a(id); data float; ); create table c ( id num primary key; lnk1 num references b(id); lnk2 num references b(id); data float; ); insert into a values (1,12.3); insert into a values (2,23.4); insert into b values (10, 1, 2, 34.5); insert into b values (20, 1, 2, 45.6); insert into b values (30, 1, 2, 56.7); insert into b values (40, 1, 2, 67.8); insert into c values (100,10,20,78.9); insert into c values (101,10,20,89.1); insert into c values (200,30,40,91.2); insert into c values (201,30,40,88.8); Request "a.b/ref1.c/lnk1" will return the following 2. complicated case for "relay-race" create table a ( id num primary key; ref1 num references b(id); ref2 num references b(id); data float; ); create table b ( id num primary key; lnk1 num references c(id); lnk2 num references c(id); data float; ); create table c ( id num primary key; data float; ); insert into с values (201,78.9); insert into с values (200,67.8); insert into с values (101,56.7); insert into с values (100,45.6); insert into b values (20, 200,201,34.5); insert into b values (10, 100,101,23.4); insert into a values (1, 10, 20, 12.3); Request "a/ref1.b/lnk1.c" will return the following ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
