Thread: 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);
bill house <wchouse@bellsouth.net> writes:
> 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
This is really getting beyond what you can do usefully with a simple
psql text file. My first suggestion would be to see if you can write
what you need as a plpgsql function.
regards, tom lane
Tom Lane wrote: > bill house <wchouse@bellsouth.net> writes: >> 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 > > This is really getting beyond what you can do usefully with a simple > psql text file. My first suggestion would be to see if you can write > what you need as a plpgsql function. > > regards, tom lane > I was afraid you were going to say that. This was the indication that I was getting from my reading, but I just wanted to make sure this trip was really necessary. Thanks, Bill House
bill house wrote: > Tom Lane wrote: >> bill house <wchouse@bellsouth.net> writes: >>> 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 >> >> This is really getting beyond what you can do usefully with a simple >> psql text file. My first suggestion would be to see if you can write >> what you need as a plpgsql function. >> >> regards, tom lane >> > > I was afraid you were going to say that. This was the indication that I > was getting from my reading, but I just wanted to make sure this trip > was really necessary. > > Thanks, > > Bill House > This is a followup on this subject with my findings re: memory variables from a close reading of the psql man page. Also a demonstration of my accidental discovery of the (undocumented?) ability to chain scripts. This ability has obvious positive implications. Thanks Bill --######################## zz_test_variable_01.sql ############ -- test of memory variables and echo output in psql -- based on reading of psql man page -- also demonstrates file chaining --set test_var_1 \set test_var_1 'this is test_var_1' --set test_var_2 \set test_var_2 'this is test_var_2' --give me a new line \echo --echo test_var_1 \echo :test_var_1 --echo test_var_2 \echo :test_var_2 --echo test_var_1 plus attempt to concatenate something \echo :test_var_1'another string' --output: this is test_var_1 another string -- ^note space, get rid of it \echo :test_var_1:test_var_2 --output: this is test_var_1 this is test_var_2 -- ^note space, get rid of it \set test_var_3 ':test_var1another string' \echo :test_var_3 --output: :test_var1another string \set test_var_4 :test_var_1 '/another string' \echo :test_var_4 --output: this is test_var_1/another string --Now that's what I'm talking about --I should be able to construct paths and file names --call another script \i zz_test_variable_01a.sql ---------------------end of zz_test_variable_01.sql --############### zz_test_variable_01a.sql ############################ -- demonstrates file chaining and availability of memory variables -- called from zz_test_variable_01.sql \echo \echo 'This script is zz_test_variable_01a.sql.' \echo 'It demonstrates the ability of psql to chain files, one calling another' \echo 'with the \i meta command.' \echo 'It was called by the script zz_test_varible_01.sql' \echo 'This script can also access memory variables set by the calling file.' \echo 'In this case, test_var_4. \echo :test_var_4 \echo -----------------------end of zz_test_variable_01a.sql All of the above yields: ================================================= world=# \i zz_test_variable_01.sql this is test_var_1 this is test_var_2 this is test_var_1 another string this is test_var_1 this is test_var_2 :test_var1another string this is test_var_1/another string This script is zz_test_variable_01a.sql. It demonstrates the ability of psql to chain files, one calling another with the i meta command. It was called by the script zz_test_varible_01.sql This script can also access memory variables set by the calling file. psql:zz_test_variable_01a.sql:11: unterminated quoted string this is test_var_1/another string world=# =================================================