variable substitution in SQL commands - Mailing list pgsql-novice

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);