title: Type error related to date conversion updating a table in a Postgres folder
body: <p>Following a data import in a Postgres database I run a function to normalise some of the data that has come in from an ODBC source. One field is a Date, but it is coming in as a varchar and for some rows it is null so when I try to convert it to a date type it fails. I realised that doesn't matter too much as the data is only used for display, but I need it to be in a useful format, so I found the following SQL:</p>
  
  <pre><code>UPDATE "MyTable" SET "VisitDate" =  to_char( DATE("VisitDate"), 'DD Mon YYYY' )  WHERE "VisitDate" &lt;&gt; '';
  </code></pre>
  
  <p>This works if I run it directly in the SQL browser, but when I execute the function it says this:</p>
  
  <pre><code>ERROR:  table row type and query-specified row type do not match
  DETAIL:  Table has type character varying at ordinal position 6, but query expects text.
  </code></pre>
  
  <p>As far as I can tell, this is because the <code>to_char()</code> function returns text, but the field is a varchar ( and given the content it is logical for it to be ) so I thought maybe I could do the following:</p>
  
  <pre><code>ALTER TABLE "MyTable" ALTER "VisitDate" TYPE "text";
  UPDATE "MyTable" SET "VisitDate" =  to_char( DATE("VisitDate"), 'DD Mon YYYY' )  WHERE "VisitDate" &lt;&gt; '';
  ALTER TABLE "MyTable" ALTER "VisitDate" TYPE varchar(12);
  </code></pre>
  
  <p>Only of course, that doesn't work in a function because - as I understand it - Postgres runs the whole thing in a single transaction so the ALTER instruction isn't happening before the UPDATE so the same error arises.</p>
  
  <p>If I try to use <code>varchar( to_char( ...etc ))</code> it just complains about the contents of the varchar function.</p>
  
  <p>Following a comment below I tried having only that line in the function and it succeeds, if I have all the table alteration statements in the function it succeeds, if I have both it fails. A basic form of the function is this:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION at_ebc_alterscorescolumns() RETURNS void AS
  $BODY$
  ALTER TABLE "MyTable" ALTER "Postcode" TYPE varchar(10);
  UPDATE "MyTable" SET "VisitDate" =  to_char( DATE("VisitDate"), 'DD Mon YYYY' )  WHERE "VisitDate" &lt;&gt; '';
  $BODY$
  LANGUAGE sql VOLATILE
  COST 100;
  </code></pre>
  
  <p>This leads me to wonder whether it is simply the case that it is not possible to alter a table and change the data in a single function.</p>
  
  <p>My question is: How can I change the formatting of the field without causing this error?</p>