agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFunction Code
2+ messages / 2 participants
[nested] [flat]
* Function Code
@ 2017-08-28 23:49 anand086 <anand086@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: anand086 @ 2017-08-28 23:49 UTC (permalink / raw)
To: pgsql-sql
Hi All,I need help in writing the a function in PostgresThe 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: http://www.postgresql-archive.org/Function-Code-tp5980522.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Function Code
@ 2017-08-29 01:34 David G. Johnston <david.g.johnston@gmail.com>
parent: anand086 <anand086@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: David G. Johnston @ 2017-08-29 01:34 UTC (permalink / raw)
To: anand086 <anand086@gmail.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2017-08-29 01:34 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-08-28 23:49 Function Code anand086 <anand086@gmail.com>
2017-08-29 01:34 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox