CODEWITHSUNDEEP
postgresqlcopyplpgsql
Peter Krauss
Peter Krauss

Reputation: 13952

Where the NOTICE or error messages?

No NOTICE neither error messages at

CREATE or replace FUNCTION copy_to_csv(
  fname text,
  query text,
  header boolean DEFAULT true,
  quotedfields text[] DEFAULT NULL,
  usedate boolean DEFAULT true
) RETURNS text AS $f$
DECLARE
  aux text :='';
BEGIN
  RAISE NOTICE 'HELLO!!!!!';
  IF p_quotedfields IS NOT NULL THEN
    aux := ', FORCE_QUOTE('|| array_to_string(quote_ident(quotedfields),',') ||')';
  END IF;
  aux := format(
    'COPY (%L) TO (%L) WITH (FORMAT CSV, HEADER %L%s)',
    query,
    CASE WHEN usedate THEN fname|| now()::date::text ELSE fname END ||'.csv',
    header,
    aux
  );
  RAISE NOTICE 'HELLO2';
  EXECUTE aux;
  RAISE NOTICE 'HELLO3';
  RETURN aux;
END;
$f$ LANGUAGE plpgsql STRICT;

... Calling with select copy_to_csv(E'select * from t', '/tmp/t');. Using PostgreSQL v10 at UBUNTU 16 LTS.

But this function is working fine:

CREATE or replace FUNCTION test1() RETURNS void AS $f$
BEGIN
  RAISE NOTICE 'HELLO!!!';
END;
$f$ LANGUAGE plpgsql STRICT;

PS: the quote_ident() overload also working fine, was implemented with

CREATE FUNCTION quote_ident(text[]) RETURNS text[] AS $f$
  SELECT array_agg(quote_ident(x)) FROM unnest($1) t(x)
$f$ LANGUAGE SQL IMMUTABLE;

Upvotes: 1

Views: 38

Answers (1)

klin
klin

Reputation: 121774

When the function is STRICT and one of the arguments is NULL, the function body is not executed and the result is NULL. Remove STRICT from the function definition.

Btw, you've mistaken the order of arguments.

Upvotes: 2

Related Questions