CODEWITHSUNDEEP
postgresqlstored-proceduressql-updatepgadmin
J10598
J10598

Reputation: 21

How to pass object (table or column) names into a stored procedure

As far as the specs to get out of the way, it is PostgreSQL 9.2, with the database through PgAdmin3, and the format of the table as a shapefile. If there are any additional details necessary I will edit and provide.

I essentially want to update integer values in a column of integer values based on whether string values in some other columns are null or not. I eventually want to port my stored function to execute in a java app using jdbc, but I first want to test my function within pgadmin3. I have very little experience with postgresql and am a little fuzzy on the syntax.

So the parameters that I've got somewhat of an idea on needing and what to put in thus far are: name, argmode, argname, argtype, column_name, and lang_name.

I understand that if I'm not returning anything I can either include RETURNS void as $$ or simply not include a return statement. I don't think I need result sets, I just want to replace integer values in a column. I'm not sure how to reference the table that I need in order to pass in the columns I want to process.

Here is the code I have cobbled together thus far: 

CREATE [OR REPLACE] FUNCTION handle_malformed([[VARIADIC][]
table_name.column_name, table_name.column_name, table_name.column_name, table_name.column_name, table_name.column_name, table_name.column_name, table_name.column_name, table_name.column_name])

BEGIN
LOOP
IF NAME_1 IS NULL THEN 
IF LEVEL_DEPT != 0 THEN
    UPDATE AdminBoundaries SET \"LEVEL_DEPT\" = 0;
ELSE IF NAME_2 IS NULL THEN 
    IF LEVEL_DEPT != 1 THEN
        UPDATE AdminBoundaries SET \"LEVEL_DEPT\" = 1;
ELSE IF NAME_3 IS NULL THEN 
    IF LEVEL_DEPT != 2 THEN
        UPDATE AdminBoundaries SET \"LEVEL_DEPT\" = 2;
ELSE IF NAME_4 IS NULL THEN 
    IF LEVEL_DEPT != 3 THEN
        UPDATE AdminBoundaries SET \"LEVEL_DEPT\" = 3;
ELSE IF NAME_5 IS NULL THEN 
    IF LEVEL_DEPT != 4 THEN
        UPDATE AdminBoundaries SET \"LEVEL_DEPT\" = 4;
ELSE
    IF LEVEL_DEPT !=5 THEN 
        UPDATE AdminBoundaries SET \"LEVEL_DEPT\" = 5;
EXCEPTION 
END LOOP;
END PROCEDURE;
$$ LANGUAGE plpgsql;

update handle_malformed(AdminBoundaries.NAME_5, AdminBoundaries.NAME_4, AdminBoundaries.NAME_3, 
AdminBoundaries.NAME_2, AdminBoundaries.NAME_1, AdminBoundaries.NAME_0, AdminBoundaries.WIKI_URL, AdminBoundaries.LEVEL_DEPT)

The logic that I put in is the logic that I put in between the loop and end loop is the logic I want to accomplish.

My specific question is, how do I use the column names from arguments in the SQL?

Upvotes: 0

Views: 1719

Answers (1)

Chris Travers
Chris Travers

Reputation: 26464

Based on the comments I have edited the question to include a question.... As I understand it, you want a value from an argument to be used in the query. To do this, use EXECUTE and assemble the query as a string. Note that you cannot parameterize identifiers of this sort and so you must concat them in, and use quote_ident() to prevent in-stored-proc sql injection.

So instead of:

 UPDATE AdminBoundaries SET \"LEVEL_DEPT\" = 1;

use:

 EXECUTE $e$ UPDATE AdminBoundaries SET $e$ || quote_ident(LEVEL_DEPT) || $e$ = 1 $e$;

You can use execute to handle dynamic checks as well:

 EXECUTE INTO my_bool $e$ SELECT a.$e$ || quote_ident(level_dept) $e$ is not null $e$;

I think this answers your question based on the comments. If not, feel free to clarify.

Upvotes: 3

Related Questions