Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group