User-defined function for string to date conversion-CodePudding

I have csv file date consisting a column date, which is in text datatype

Date
(text)
-----------
 date1
 date2
 date3

I need to change the column into date datatype. SO I tried this method.

create or replace function fn_date_conversion (
 get_column text
) 
    returns table (
        
        datecolumn date
    ) 
    language plpgsql
as $$
begin
    return query 
        SELECT TO_DATE(get_column,'YYYY-MM-DD')
        from tblsalesdata;
end;
$$

SELECT fn_date_conversion(date)

but it throws an error

ERROR: column "date" does not exist
LINE 1: SELECT fn_date_conversion(date)

When I try to make function without parameter with following query it work

   create or replace function fn_date_conversion (
 
) 
    returns table (
        
        datecolumn date
    ) 
    language plpgsql
as $$
begin
    return query 
        SELECT TO_DATE(date,'YYYY-MM-DD')
        from tblsalesdata;
end;
$$

but I need to return parameter so I can convert date dynamically not statically as I have other date column which are in text type too. How to fix this?

CodePudding user response：

If the the column name is dynamic, then you need to use dynamic SQL

So you need function like:

CREATE OR REPLACE FUNCTION foo(colname text, tblname text)
RETURNS TABLE(datecol date)
AS $$
BEGIN
  RETURN QUERY EXECUTE
    format($_$SELECT to_date(%I, 'YYYY-MM-DD') FROM %I$_$,
           colname, tblname);
END;
$$ LANGUAGE plpgsql;

SELECT * FROM foo('colname','tblsalesdata');