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
– joop, Commented Jul 24, 2015 at 10:47

Patrick · Accepted Answer · 2015-07-24 11:25:11Z

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.

smile · Accepted Answer · 2015-07-24 14:38:54Z

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, 2015 at 14:38

smile

1021 silver badge9 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:13:59Z

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, 2017 at 12:13

CommunityBot

11 silver badge

answered Jul 24, 2015 at 10:49

Renzo

27.6k5 gold badges54 silver badges64 bronze badges

2 Comments

smile Over a year ago

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.

Renzo Over a year ago

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.

Collectives™ on Stack Overflow

PostgreSQL casting issue on data type

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related