variable substitution in SQL commands

From: bill house <wchouse(at)bellsouth(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: variable substitution in SQL commands
Date: 2010-01-31 21:03:19
Message-ID: 4B65F017.1090101@bellsouth.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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);

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2010-01-31 21:06:20 Re: variable substitution in SQL commands
Previous Message Pushpendra Singh Thakur 2010-01-31 19:50:53 Re: Error installing pljava