COPY with dynamic file name
You need dynamic SQL:
CREATE OR REPLACE FUNCTION loaddata(filepathname text)
RETURNS void AS
$func$
BEGIN
EXECUTE format ('
COPY climatedata(
climatestationid
, date
... -- more columns
, tminsflag)
FROM %L (FORMAT CSV, HEADER)' -- current syntax
-- WITH CSV HEADER' -- tolerated legacy syntax
, $1); -- pass function parameter filepathname to format()
END
$func$ LANGUAGE plpgsql;
format()
requires PostgreSQL 9.1+.
Pass the file name without extra set of (escaped) single quotes:
SELECT loaddata('/absolute/path/to/my/file.csv')
format()
with %L
escapes the file name safely. Would be susceptible to SQL injection without it.
Aside, you have a function name mismatch:
CREATE OR REPLACE FUNCTION public.loaddata(filepathname varchar)
...
ALTER FUNCTION public.filltmaxa(character varying)
export csv with dynamic file name
Try composing the filename like this:
import os
filename = os.path.join('F:', 'Result_Check', 'B933418' + f + '.csv')
Also you do not normally assign result of df.to_csv to a variable.
COPY csv using custom filename path
plpgsql code could work like this:
...
DECLARE
var1 text;
BEGIN
var1 := to_char(current_date - 1, 'YYYY-MM-DD');
EXECUTE $$COPY (SELECT * from myTable)
TO E'C:\\Exports\\export_$$ || var1 || $$.csv' WITH CSV$$;
...
Your quotes got tangled. Using dollar-quoting to simplify. Note that syntax highlighting here on SO is misleading because it does not understand dollar-quoting.
DECLARE
is only needed once (not an error though). Plus, BEGIN
was missing.
And to_char()
makes the text representation of a date independent of the locale.
How to copy a csv file using SQL
You'll have to use dynamic SQL, since COPY
doesn't support parameters:
EXECUTE format(
'COPY match_import FROM %L (FORMAT ''csv'', DELIMITER '','', HEADER)',
filename
);
How to dynamically name output of a sql file in sqlcmd
Give this a try:
set timehour=%time:~0,2%
sqlcmd -S INSTANCENAME -i c:\Users\name\Desktop\test.sql -o c:\Users\name\Desktop\name-%date:~-4,4%%date:~-10,2%%date:~-7,2%-%timehour: =0%%time:~3,2%.csv
You can try seeing the output by using Echo
set timehour=%time:~0,2%
echo name-%date:~-4,4%%date:~-10,2%%date:~-7,2%-%timehour: =0%%time:~3,2%.csv
%date% returns current date in short format. The ":~6,4" part is like a
SUBSTRING function which returns 4 characters starting from position
6, which returns Year. Similarly, retrieving month, day, hour,
minutes using same function and appending all of this together to
generate the file name in format "name-YYYYMMDD-HHMM"
SSIS - How to design output file-name (CSV) as a Dynamic Project Parameter?
User:Jo Douglass gave the logical solution to the problem. This answer to a SO question gives a detailed rundown on why we cannot use Expressions in Project Parameters and an alternative approach to solve the issue.
Related Topics
Update Xml Tag in a Clob Column in Oracle
How to Select All Values and Hide Null Values in SQL
Custom Order by in SQL Server Like P, A, L, H
SQL Queries on String Columns - Sorting According to Language
How to Use Merge on Linked Servers
SQL Query to Bring Last Letter in a String to First Letter Position
How to Copy Data from One Table to Another in Postgres Using Copy Command
Select All Threads and Order by the Latest One
Differencebetween a Candidate Key and a Primary Key
T-Sql: Separate String into Multiple Columns
SQL Missing Right Parenthesis on Order by Statement
What Is a Good Reason to Use SQL Views
Pivot a Table on a Value But Group the Data on One Line by Another
Db2 - Returning the Top 5 of Each Category
Need to Find Average Processing Time Between All Timestamp Records in Oracle SQL