Postgresql – Is it possible to write a function in postgresql which directly writes its parameters to a text file

functionspostgresql

I have a text file which contains some rows of data,say 10 rows. Each row has 5 columns, each of the type varchar. Is it possible to write a function in postgresql, which takes its parameters as 5 varchar values, and appends those values to that file directly?

Best Answer

Using COPY, this boils down to one simple SQL command:

COPY (SELECT 'a','b','c','d','e') TO PROGRAM 'cat >> /path/to/file/my.csv';

You can wrap it into a function if required:

CREATE OR REPLACE FUNCTION public.f_dump_row(varchar,varchar,varchar,varchar,varchar)
  RETURNS void LANGUAGE plpgsql AS
$func$
BEGIN
EXECUTE format($$COPY (SELECT %L,%L,%L,%L,%L) TO PROGRAM 'cat >> /path/to/file/my.csv'$$
             , $1,$2,$3,$4,$5); 
END
$func$;

Why the complication with EXECUTE?

How to set column DEFAULT using value of a variable

cat is a standard UNIX utility (shell command).

Your Postgres role needs appropriate privileges. The manual:

COPY naming a file or command is only allowed to database superusers or users who are granted one of the default roles pg_read_server_files, pg_write_server_files, or pg_execute_server_program, since it allows reading or writing any file or running a program that the server has privileges to access.

If you need this for under-privileged roles, consider a SECURITY DEFINER function. But do it properly as instructed in the manual to avoid misuse.

And the target file must be writable by the system user running the Postgres process, typically postgres.

copy command in postgresql to append data

Related Solutions

Postgresql – Restricting Insert and Update to functions

Yes this is possible.

You (obviously) need two database users for this:

one who is allowed to call the function
one who ones the table and is allowed to directly modify it.

Then revoke the update, insert and delete privilege from the restricted user.

Then create the function with the privileged user as the owner and use the attribute SECURITY DEFINER when creating it.

Thus the restricted user cannot change the table, but when he calls the function, the function runs with the privileges of the other user and thus can modify the table.

Something like this:

Logged in as the privileged user:

create user restricted_user password 'hidden';
create table foo (id serial not null primary key, some_info text);
grant select on foo to restricted_user;
create or replace function public.insert_foo(p_info text)
  RETURNS void
  security definer  -- << this is important
  LANGUAGE sql
AS
$body$
   insert into public.foo (some_info) values (p_info);
$body$;

grant execute on function public.insert_foo(text) to restricted_user;

Now you log in as the restricted_user:

insert into foo (some_info) values ('foo');
==> ERROR: permission denied for relation foo

select insert_foo('foo');
==> one row inserted

PostgreSQL Views – Writing a View that Caches to Another Table

CREATE OR REPLACE FUNCTION trade_volume (_market_id  int
                                       , _start_time timestamp
                                       , _end_time   timestamp)
  RETURNS SETOF numeric(16,8) AS
$func$
BEGIN

RETURN QUERY
SELECT sum                          -- dubious identifier
FROM   market_trades_sum_cache
WHERE  market_id  = _market_id
AND    start_time = _start_time
AND    end_time   = _end_time;

IF NOT FOUND THEN
   RETURN QUERY
   INSERT INTO market_trades_sum_cache(market_id, start_time, end_time, sum)
   SELECT market_id
        , _start_time
        , _end_time
        , sum(amount)
   FROM   market_trades
   WHERE  market_id = _market_id
   AND    timestamp BETWEEN _start_time AND _end_time
   GROUP  BY 1
   RETURNING sum;
END IF;

END
$func$ LANGUAGE plpgsql VOLATILE;

A couple of warnings

It's a bad idea to use names of basic functions (sum) or data types (timestamp) as column names.
BETWEEN includes lower and upper bound. Often, you would rather include the lower, but exclude the upper bound.
There is a tiny chance for a race condition in the above function. To be prepared for heavy concurrent load, consider this related answer:
- Is SELECT or INSERT in a function prone to race conditions?

Alternative with `tsrange`

...
AND    ts_range = tsrange(_start_time, _end_time, '[]')
...
   INSERT INTO market_trades_sum_cache(market_id, ts_range, sum)
   SELECT market_id
        , tsrange(_start_time, _end_time, '[]')  -- incl. bounds
        , sum(amount)
   FROM   market_trades
   WHERE  market_id = _market_id
   AND    timestamp BETWEEN _start_time AND _end_time
   GROUP  BY 1
   RETURNING sum;
...

I would consider a multicolumn GIN or GiST index for the range type. Though, if you only check for equality, a btree index would do the job, too. But the latter isn't much use for anything else. Details in the manual.

To include market_id in the index (like I'd suggest), you also need the additional module btree_gin or btree_gist. Then create the index:

CREATE INDEX sum_mult_idx ON market_trades_sum_cache USING GIN (market_id, ts_range, sum);

The last column sum is optional and only makes sense if you get index-only scans out of it. Else don't include it in the index.

Or, if you want to disallow overlapping time ranges, use an exclusion constraint, based on a GiST index (GIN is not currently possibly). Details:

The last one also provides a clean solution for enforcing '[)' boundaries.