0
votes

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;
2
you are missing a space after the from: from '||... - a_horse_with_no_name
Strong advice to use the format() function and quote_ident() instead of string concatenation. - joop
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 - Elizabeth Irvin
Try changing 'where c_name=%I' to 'where c_name=%L' - Ihor Romanchenko
Other option: where c_name='''||tblname||''''; - Ihor Romanchenko

2 Answers

1
votes

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.

0
votes

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.