Re: Function Code

On Mon, Aug 28, 2017 at 4:49 PM, anand086 <anand086(at)gmail(dot)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.