CODEWITHSUNDEEP
postgresql
smile
smile

Reputation: 102

PostgreSQL casting issue on data type

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

Upvotes: 0

Views: 449

Answers (3)

smile
smile

Reputation: 102

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

Typecast the value as character varying

Upvotes: 1

Patrick
Patrick

Reputation: 32161

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.

Upvotes: 1

Renzo
Renzo

Reputation: 27404

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 $$ .

Upvotes: 0

Related Questions