0

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;
8
  • you are missing a space after the from: from '||... Commented Mar 22, 2016 at 17:00
  • 1
    Strong advice to use the format() function and quote_ident() instead of string concatenation. Commented Mar 22, 2016 at 17:02
  • 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 Commented Mar 22, 2016 at 17:11
  • Try changing 'where c_name=%I' to 'where c_name=%L' Commented Mar 22, 2016 at 17:20
  • Other option: where c_name='''||tblname||''''; Commented Mar 22, 2016 at 17:21

2 Answers 2

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.

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

Comments

0

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.

4 Comments

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.
Yes, the last || must go. Identifiers may need double quotes, never single quotes. Should be done with quote_ident() or with format() and %I.
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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.