syntax error when calling plpgsql function

Question

I'm working on a project that involves calculating the percent of an industry cluster's cost structure that comes from in-region transportation costs. I'll have one table for each industry cluster with the detailed cost breakdown (naics, amount, inregion_amt), a lookup table transpo_industries with all transportation naics, and a summary table cluster_costs that I want to eventually contain each industry cluster's name (c_name), the total cost (tot_cost), and the in-region transportation costs (inregion_transpo). The table is already populated with all the industry names, which match the table names for the corresponding industry clusters.

Since I need to run through at least 15 industry clusters and would potentially like to re-run this code with smaller subsets of the data, I'm trying to create a function. The following code creates the function without error, but when I try to call it, I get a syntax error ("ERROR: syntax error at or near "clustercosts" SQL state: 42601")

Can anyone help point out where I'm going wrong?

create or replace function clustercosts(tblname text) RETURNS void
AS $$
BEGIN
EXECUTE 'update cluster_costs set tot_cost= (select sum(amount) from '||tblname||'), inregion_transpo = (select sum(inregion_amt) from '||tblname||', transpo_industries where '||tblname||'.naics=transpo_industries.naics) where c_name='||tblname||;
END;
$$ Language plpgsql;

A version using format() gives me the same error:

CREATE OR REPLACE FUNCTION udate_clustercosts(tblname text)
 RETURNS void AS
$BODY$
BEGIN
EXECUTE format(
'update cluster_costs'
'set tot_cost= (select sum(amount)from %I),'
'inregion_transpo = (select sum(inregion_amt) from %I, transpo_industries where %I.naics=transpo_industries.naics)'
'where c_name=%I',tblname);
END;
$BODY$
 LANGUAGE plpgsql;

• user330315: you are missing a space after the from: from '||...
• joop: Strong advice to use the format() function and quote_ident() instead of string concatenation.
• Elizabeth Irvin: I added the spaces (code edited above), but I'm still getting the same error. I also actually tried a version with format() before I tried the string concatenation and was having the same issues
• Ihor Romanchenko: Try changing 'where c_name=%I' to 'where c_name=%L'
• Ihor Romanchenko: Other option: where c_name='''||tblname||'''';

Answer 1

Your problems start at the design stage. With a proper DB design you wouldn't need dynamic SQL for this to begin with.

I'll have one table for each industry cluster ...

Don't. This should be a single table (like cluster_details) with a FK column (like cluster_id) referencing the PK of the table listing industry clusters (like industry_cluster).

It's also questionable that you materialize a computed aggregate with your UPDATE. Use a VIEW (or function) instead to get current sums. Your base query would be something like:

SELECT ic.*
 , sum(cd.amount) AS sum_amount
 , (SELECT sum(inregion_amt)
 FROM transpo_industries
 WHERE naics = cd.naics) AS sum_inregion_amt
FROM industry_cluster ic
LEFT JOIN cluster_details cd USING (cluster_id)
WHERE ic.name = 'Cluster 1';

As for the question asked: since the error is triggered by the function call and the error message clearly references the function name, the problem lies with the call, which is missing in the question.

There are other problems in your function definition, as has been pointed out in the comments - none of which are related to the error message you presented.

Answer 2

You have been bitten by the fact, that you want to use single quotes within quoted string. You can avoid that using dollar-quoted string constants as explained in the documentation.

The problem arises because you want to use single quote within SQL statement, because you want to pass value of tblname as a string constant.

Here I use $a$ to quote within the function body, quoted with $$:

create or replace function clustercosts(tblname text) RETURNS void
AS $$
BEGIN
EXECUTE $a$ update cluster_costs set tot_cost= (select sum(amount) from $a$ || tblname || $a$), inregion_transpo = (select sum(inregion_amt) from $a$ || tblname || $a,ドル transpo_industries where $a$ || tblname || $a$.naics=transpo_industries.naics) where cluster_costs.c_name='$a$ || tblname || $a$'$a$;
END;
$$ language plpgsql;

It's valid to insert nearly any identifier between the dollar signs and is a common pattern for nesting quotes in functions, exactly as in your case.

---

Example

I create the tables you describe:

create table tblname (naics int, amount int, inregion_amt int);
create table transpo_industries (naics int);
create table cluster_costs (c_name text, tot_cost int, inregion_transpo int);
testdb=> SELECT clustercosts('tblname');
 clustercosts 
--------------
(1 row)

No errors, SQL executed.

• Erwin Brandstetter: I don't see any problems with quotes in the question. I take it you are aware that string constants that are only separated by whitespace with at least one newline are concatenated and effectively treated as if the string had been written as one constant - as per SQL standard.
• hruske: Yes, exactly! That's why where c_name='||tblname||; does not make sense - the tblname should be quoted, but the author can't use single quote, because the string quoting is already using them. Also, || is not an unary operator.
• Erwin Brandstetter: Yes, the last || must go. Identifiers may need double quotes, never single quotes. Should be done with quote_ident() or with format() and %I.
• hruske: No, because this is in WHERE and you can't put table name there, it does not make a valid SQL expression. Even if one would use a regclass for this, it would still need to be quoted (and cast).