PostgreSQL casting issue on data type

Question

Here is my code on that I have dynamic column name while I joined to other tables data type mismatch will occur. How can I resolve this issue..

CREATE OR REPLACE FUNCTION migratePartnerAdvertiser() RETURNS int4 AS '
DECLARE r RECORD;
BEGIN
  FOR r IN
    select distinct COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
    where TABLE_NAME = ''casesheetcomplaints'' and table_schema=''public''
  LOOP
    EXECUTE concat(''INSERT INTO patient_form_value(patient_form_id,
                                                    form_template_field_id, field_value) 
      select  pf.id AS patient_form_id, ftf.id AS form_template_field_id, c.'',
              r.column_name, '' AS field_value  
      FROM casesheetcomplaints c 
      LEFT OUTER JOIN form_field ff ON ff.field_name = '',r.column_name,''
      LEFT OUTER JOIN form_template_field ftf ON ftf.form_field_id = ff.id
      LEFT OUTER JOIN patient_form pf on c.patient_id = pf.patient_id
                                      AND  pf.created_date = c.reg_date 
      where c.'', r.column_name, '' IS NOT NULL AND ftf.id IS NOT NULL
      GROUP BY c.patient_id, c.'', r.column_name, '', pf.id, ftf.id'');
  END LOOP;
  return 1;
END;
' LANGUAGE plpgsql;

Error message:

ERROR: operator does not exist: character varying = numeric LINE 4: LEFT OUTER JOIN form_field ff ON ff.field_name = weight ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. QUERY: INSERT INTO patient_form_value(patient_form_id, form_template_field_id, field_value) select pf.id AS patient_form_id, ftf.id AS form_template_field_id, c.weight AS field_value FROM casesheetcomplaints c LEFT OUTER JOIN form_field ff ON ff.field_name = weight LEFT OUTER JOIN form_template_field ftf ON ftf.form_field_id = ff.id LEFT OUTER JOIN patient_form pf on c.patient_id = pf.patient_id AND pf.created_date = c.reg_date where c.weight IS NOT NULL AND ftf.id IS NOT NULL GROUP BY c.patient_id, c.weight,pf.id,ftf.id

To reduce quoting problems, you could use the `format()` function in stead of the concat() construct (otherwise you could use quote_ident() and quote_literal() ) — joop, Jul 24 '15 at 10:47

Patrick · Answer 1 · 2015-07-24T11:25:11.343

Do yourself a favour and:

Use dollar-quoting for your function body
Properly format your function body
Use the format() function instead of concat()
Use CAPITALS for SQL identifiers and lower-case for everything else

If you do all of the above, your function becomes the rather more readable:

CREATE FUNCTION migratePartnerAdvertiser() RETURNS int4 AS $$
DECLARE
  r RECORD;
BEGIN
  FOR r IN
    SELECT column_name FROM information_schema.columns
    WHERE table_name = 'casesheetcomplaints' AND table_schema = 'public'
  LOOP
    EXECUTE format('
      INSERT INTO patient_form_value(patient_form_id, form_template_field_id, field_value) 
        SELECT pf.id AS patient_form_id,
               ftf.id AS form_template_field_id,
               c.%1$I AS field_value  
        FROM casesheetcomplaints c 
        LEFT JOIN form_field ff ON ff.field_name = %1$L
        LEFT JOIN form_template_field ftf ON ftf.form_field_id = ff.id
        LEFT JOIN patient_form pf ON c.patient_id = pf.patient_id AND pf.created_date = c.reg_date 
        WHERE c.%1$I IS NOT NULL AND ftf.id IS NOT NULL', r.column_name);
  END LOOP;
  RETURN 1;
END;
$$ LANGUAGE plpgsql;

Note that r.column_name is listed at the end of the format() function and is inserted in the query wherever a % is encountered. That happens multiple times, hence the %1$ positional parameter. You use it both as an identifier %1$I and as a literal %1$L, which should get rid of your error.

I also removed the GROUP BY clause because you do not aggregate on anything. If you want to remove duplicates, use a DISTINCT clause instead. The DISTINCT clause in the loop query is useless: a single table cannot have multiple columns with the same name.

score 1 · Accepted Answer · answered Jul 24 '15 at 14:38

1

LEFT OUTER JOIN form_field ff ON ff.field_name = cast('',r.column_name,'' as character varying)

Typecast the value as character varying

answered Jul 24 '15 at 14:38

smile

102
1
9

score 0 · Answer 3 · edited May 23 '17 at 12:13

0

Note that the error is due to:

ON ff.field_name = weight

that I imagine should be:

ON ff.field_name = 'weight'

So you have to use a way of escaping single quote. See this question on how to insert single quotes.

Another suggestion is: never use ' to delimit the body of a function, since it conflicts with the need of using single quotes inside the body, use instead the more common $$ .

edited May 23 '17 at 12:13

Community

1
1

answered Jul 24 '15 at 10:49

Renzo

26,848
5
49
61

Ya but the values (weight) will be a column for some other db its data type will differ.so mismatched the datatyes.How can I handle multiple datatypes on that. – smile Jul 24 '15 at 11:00
But do you want to compare field_name with the *value* of the field `weigth` or with the name of that field (i.e. "weigth")?. By reading ff.field_name I understand you want to compare it with the name. – Renzo Jul 24 '15 at 11:02

PostgreSQL casting issue on data type

3 Answers3