Thread: [SQL] Function Code

[SQL] Function Code

From

anand086

Date:

29 August 2017, 05:49:37

Hi All, I need help in writing the a function in Postgres The function does -- • Generate the 32 hex characters using encode(digest('Welcome123', 'md5'), 'hex'). • Then picks up last 16 characters. I used SELECT SUBSTR(5858ea228cc2edf88721699b2c8638e5, 17, 16) • From these 16 characters, first 8 characters and second 8 characters are bundled and reversed with in those 8 characters. • Then reversed characters are concatenated. As an example, I tried writing the below code but I am stuck and would need help to write it in correct way --

CREATE OR REPLACE FUNCTION get_code(bytea) 
returns text AS 
$$SELECT encode(digest($1, 'md5'), 'hex') INTO l_hex_32;SELECT SUBSTR(l_hex_32, 17, 16) INTO l_low_16;SELECT SUBSTR(l_low_16, 1, 8) INTO l_q3;SELECT SUBSTR(l_low_16, 9, 8) INTO l_q4;SELECT SUBSTR(l_q3, 7, 2)||SUBSTR(l_q3, 5, 2)||SUBSTR(l_q3, 3, 2)||SUBSTR(l_q3, 1, 2) INTO l_q3;   SELECT SUBSTR(l_q4, 7, 2)||SUBSTR(l_q4, 5, 2)||SUBSTR(l_q4, 3, 2)||SUBSTR(l_q4, 1, 2) INTO l_q4;   SELECT l_q3||l_q4 into l_low_16_m;
$$  LANGUAGE SQL STRICT IMMUTABLE;

In the above code I want to finally return the value of l_low_16_m. Regards, Anand

View this message in context: Function Code
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

Re: [SQL] Function Code

From

"David G. Johnston"

Date:

29 August 2017, 07:34:13

On Mon, Aug 28, 2017 at 4:49 PM, anand086 <anand086@gmail.com> wrote:

Hi All, I need help in writing the a function in Postgres The function does -- • Generate the 32 hex characters using encode(digest('Welcome123', 'md5'), 'hex'). • Then picks up last 16 characters. I used SELECT SUBSTR(5858ea228cc2edf88721699b2c8638e5, 17, 16) • From these 16 characters, first 8 characters and second 8 characters are bundled and reversed with in those 8 characters. • Then reversed characters are concatenated. As an example, I tried writing the below code but I am stuck and would need help to write it in correct way --
CREATE OR REPLACE FUNCTION get_code(bytea) 
returns text AS 
$$SELECT encode(digest($1, 'md5'), 'hex') INTO l_hex_32;SELECT SUBSTR(l_hex_32, 17, 16) INTO l_low_16;SELECT SUBSTR(l_low_16, 1, 8) INTO l_q3;SELECT SUBSTR(l_low_16, 9, 8) INTO l_q4;SELECT SUBSTR(l_q3, 7, 2)||SUBSTR(l_q3, 5, 2)||SUBSTR(l_q3, 3, 2)||SUBSTR(l_q3, 1, 2) INTO l_q3;   SELECT SUBSTR(l_q4, 7, 2)||SUBSTR(l_q4, 5, 2)||SUBSTR(l_q4, 3, 2)||SUBSTR(l_q4, 1, 2) INTO l_q4;   SELECT l_q3||l_q4 into l_low_16_m;
$$  LANGUAGE SQL STRICT IMMUTABLE;
In the above code I want to finally return the value of l_low_16_m. Regards, Anand

At a basic level you need to learn the differences between a pure SQL function (which is what you declare this function to be) and a PL/PGSQL function (which seems to be what you want to write given your use of variables and the INTO command modifier). You can write this function in either, the pure SQL version is going to use subselects (i.e., SELECT * FROM (SELECT * FROM ) subselect) while the pl/pgsql version can indeed make use of temporary variables.

The documentation covers both of these function writing languages (though SQL is basically just wrapping SQL in a function and its returns the last statement executed) in great depth. If you change the LANGUAGE portion and add the required BEGIN/DECLARE/END lines you'll probably be most if not all the way there.

David J.