make_hash() function? hash codes?

Question

My boss sent me a process, in to which I need to:

make a function that creates a temp table
grab data from a table
use a function to refine the data
create a hash code for 2 of the columns
insert all the data from the temp to the original data
drop the table

I've been trying to look what the make_hash() function does. Whenever I run the FUNCTION it gives me an error on that line, so I'm guessing I need to declare it first before the begin statement in the create function. Or is it a user-created function? If so, is there a way for me to find which schema the function is located?

I've been trying to research in the books and online but I couldn't find anything, even in the PostgreSQL website.

--------------------------------------------

ERROR:  function make_hash(character varying) does not exist
LINE 1: UPDATE temp_match_queue_04022012 SET title_hash = make_hash(...
                                                          ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  UPDATE temp_match_queue_04022012 SET title_hash = make_hash(as_title) WHERE as_title IS NOT NULL
CONTEXT:  PL/pgSQL function "metadata_matching_temp" line 30 at SQL statement

********** Error **********

ERROR: function make_hash(character varying) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Context: PL/pgSQL function "metadata_matching_temp" line 30 at SQL statement

Updated! Sorry, but the query error I posted earlier was a different one. — John V
– John V, Commented Apr 3, 2012 at 17:16
@JohnV you should post your solution as an answer and accept it, rather than just a comment. — Jordan
– Jordan, Commented Apr 3, 2012 at 21:28
I can't yet since it says I'm still new. Will update it definitely! — John V
– John V, Commented Apr 3, 2012 at 21:49

Angelo Fuchs · Accepted Answer · 2012-04-03 21:18:12Z

1

As @Clodoaldo mentioned look into pgcrypto (which you would get by installing it to your postgres, on debian its in postgresql-contrib iirc).

Any function you call must be available before the BEGIN section. You can combine functions into packages if you need more than one.

answered Apr 3, 2012 at 21:18

Angelo Fuchs

9,9911 gold badge39 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

John V Over a year ago

I'll take note of this. Thanks Angelo! I'll read more in to the pgcrypto link Clodoaldo sent me.

John V Over a year ago

Ah, and what do you mean by having the function available before the begin section??

Angelo Fuchs Over a year ago

@JohnV I meant that before your code is called (isn't it a function itself? I thought so.) the function must already be known to the server.

John V Over a year ago

I believe so. But how I made it work was i needed to type in the schemaname.make_hash() inside the function I created.

John V · Accepted Answer · 2012-04-04 16:31:26Z

1

Nevermind! The make_hash function was written psql that's why it wasn't working properly. I'm using pgAdmin III and the schema name had to be called together with the function for it to work. Thanks a bunch for the help though!

answered Apr 4, 2012 at 16:31

John V

632 silver badges9 bronze badges

Collectives™ on Stack Overflow

make_hash() function? hash codes?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related