variable substitution in SQL commands

Hello,

I am trying to learn how to construct SQL commands using information
derived from other SQL commands/querys.

These commands are stored for the moment in a simple text file which
would be executed by the psql client like so:

current_database=# \i sql_command_file.sql

In this file I want to store a path name to a data directory in a
variable and then concatenate that path with a file name (of a csv file)
to then copy said file into a table.

I have not been able to discover a straightforward way to do this so as
a work-around, I created a table and stored the information there to use
later.

It works, but

1) Is there a better way to do this?

Now that that works,

2) I would like to take the results of a query extracting/constructing
the desired data (path/file) and inserting that data into another SQL
command like so:

COPY test_table FROM (SELECT constructed path/file) WITH CSV;

My research has indicated that there may be several approaches and I
have tried several that I thought I understood, but I don't have command
of all the nuances of constructing SQL statements.

The following is a file containing my commands with various trials and
results.

Any guidance would be appreciated.

Thanks,

Bill

=================================================

-----set path for cvs data
--see http://www.pgadmin.org/docs/dev/pgscript.html
--declare @data_path character varying (40);
--set @data_path = '/home/some_user/test_data';
--Nope! use a table

--create a schema or name space
CREATE SCHEMA testing;
--comment out if it already exists

DROP TABLE testing.mem_var;
CREATE TABLE testing.mem_var
(
mem_name character varying (40) PRIMARY KEY,
mem_content character varying (1024)

);

--add my path variable
INSERT INTO testing.mem_var VALUES (
'data_path',
'/home/some_user/test_data'
);

--create the tables

--############################# test_table ############################
/*
--Commented out for the present
DROP TABLE testing.test_table;
CREATE TABLE testing.test_table
(
field_01 character varying (10),
field_02 character varying (6),
field_03 character varying (12)

);
*/

--Doesn't work: SELECT mem_content FROM testing.mem_var
-- WHERE mem_name = 'data_path';
--Doesn't work: SELECT mem_content FROM testing.mem_var
-- WHERE mem_name = 'data_path' ||

/*This works!

SELECT mem_content || '/test_table.csv' AS cvs_file FROM
testing.mem_var WHERE mem_name = 'data_path';

returns:
cvs_file
------------------------------------------------------------------------------
/home/some_user/test_data/test_table.csv
(1 row)

--Put parenthesis around my value to return to make it clearer what I am
doing.
--Still works.

SELECT (mem_content || '/test_table.csv') AS cvs_file FROM
testing.mem_var WHERE mem_nam = 'data_path';

Now to substitute the above query (or it's results) into an SQL command
COPY etc., etc.,
*/

-- Does not work.
COPY testing.test_table
FROM (SELECT mem_content || '/test_table.csv'
AS cvs_file
FROM testing.mem_var
WHERE mem_name = 'data_path')
WITH CSV;

/*
Hmmmm. Could use mem_var table here too i.e.
retrieve path
construct path/file string and store
retrieve path_file
construct whole statement and store
retrieve statement and execute
Maybe as a last resort if I don't figure this out.
See also 8.3 manual pages 575~6 Dynamic SQL
pages 600~1 Using SQL Descriptor Areas
psql client
see man page %`command`
\set

OK, I give up. Use the mem_var table
*/

/*
Nope!
INSERT INTO testing.mem_var VALUES (
'path_file',
SELECT (mem_content || '/test_table.csv')
AS cvs_file
FROM testing.mem_var
WHERE mem_name = 'data_path'
);
*/

/*
Nope!
INSERT INTO testing.mem_var VALUES (
'path_file',
SELECT (mem_content || '/test_table.csv')
FROM testing.mem_var
WHERE mem_name = 'data_path'
);
*/

/*
Nope!
SELECT (mem_content || '/test_table.csv')
AS cvs_file
FROM testing.mem_var
WHERE mem_nam = 'data_path';
INSERT INTO testing.mem_var VALUES (
'path_file',
cvs_file
);
*/

/*
Nope!, at least not in this form
--from http://www.faqs.org/docs/ppbook/x5504.htm
-- Practical Postgresql Ch 4 Adding Data with INSERT and COPY
INSERT INTO testing.mem_var (mem_name, mem_content)
'path_file',
SELECT (mem_content || '/test_table.csv')
AS cvs_file
FROM testing.mem_var
WHERE mem_name = 'data_path'

--Maybe insert the record, then update it with the value
*/

INSERT INTO testing.mem_var VALUES (
'path_file',
''
)

--BEGIN/COMMIT? Man page 18

UPDATE testing.mem_var
SET mem_content =
SELECT (mem_content || '/test_table.csv')
AS cvs_file
FROM testing.mem_var
WHERE mem_name = 'data_path')
WHERE mem_name - 'path_file);