pgsql function sample

pgfunction sample

-- Table: bytea_test

-- DROP TABLE bytea_test;

CREATE TABLE bytea_test
(
  md5_key bytea
)
WITH (
  OIDS=FALSE
);
ALTER TABLE bytea_test
  OWNER TO meduser;


-- Function: update_bytea_test(text)

-- DROP FUNCTION update_bytea_test(text);

CREATE OR REPLACE FUNCTION update_bytea_test(IN v_md5_hex_string text, OUT v_result integer)
  RETURNS integer AS
$BODY$ 
DECLARE     
BEGIN 	
    BEGIN 		
        INSERT INTO bytea_test(md5_key) VALUES ( decode(v_md5_hex_string, 'hex') ); 			
            v_result := 1; 		
        EXCEPTION WHEN unique_violation THEN 			
            v_result := 0; 		
        END; 
    END; 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION update_bytea_test(text)
  OWNER TO meduser;

  
  
-- Function: update_bytea_test_out_2(text)

-- DROP FUNCTION update_bytea_test_out_2(text);

CREATE OR REPLACE FUNCTION update_bytea_test_out_2(IN v_md5_hex_string text, OUT v_result integer, OUT v_result2 integer)
  RETURNS record AS
$BODY$ 
DECLARE     
    BEGIN 	
        BEGIN 		
            INSERT INTO bytea_test(md5_key) VALUES ( decode(v_md5_hex_string, 'hex') ); 			
                v_result := 2; 			
                v_result2 := 3; 		
            EXCEPTION WHEN unique_violation THEN 			
                v_result := 0; 			
                v_result2 := 0; 		
            END; 
        END; 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION update_bytea_test_out_2(text)
  OWNER TO meduser;
  
  
  
//> call function
select * from update_bytea_test('00AABBCCDDEEFFAABBCCDDEEFFAABBCCDDEEFFAABBCCDDEEFFAABBCCDDEEFFAABBCCDDEEFF' );
select * from update_bytea_test_out_2('AABBCCDDEEFFAABBCCDDEEFFAABBCCDDEEFFAABBCCDDEEFFAABBCCDDEEFFAABBCCDDEEFF' );

//> select 
-- insert data (hex string) -> bytea
insert into bytea_test values ( decode('ff00112233445566778899AABBCCDDEEFF', 'hex') );

-- get data bytea -> as hex_string(recomended, fast) / escape(pg legacy) / base64
select encode(md5_key, 'hex')  from bytea_test;
select encode(md5_key, 'escape')  from bytea_test;
select encode(md5_key, 'base64')  from bytea_test;