ddl - Concat variable with a string in location parameter in a create statement?

Question

In Greenplum, I need to create an external table with a dynamic location parameter. For an example:

CREATE READABLE TABLE_A(
date_inic date,
func_name varchar,
q_session bigint
)
LOCATION(:location)
FORMAT 'TEXT' (DELIMITER '|');

But in :location parameter I need to concat it with a fixed string. I tried:

LOCATION (:location || '123')

But I get a syntax error, otherwise in select statement it works perfectly. I'm inserting :location value like: " 'gphdfs://teste:1010/tmp' "

Can anyone help me?

Answer 1

You are missing a few things in your table definition. You forgot "external" and "table".

CREATE READABLE EXTERNAL TABLE table_a
(
date_inic date,
func_name varchar,
q_session bigint
)
LOCATION(:location)
FORMAT 'TEXT' (DELIMITER '|');

Note: gphdfs has been deprecated and you should use PXF or gpfdist instead.

Next, you just need to use double quotes around the location value.

[gpadmin@mdw ~]$ psql -f example.sql -v location="'gpfdist://teste:1010/tmp'"
CREATE EXTERNAL TABLE
[gpadmin@mdw ~]$ psql 
psql (9.4.24)
Type "help" for help.

gpadmin=# d+ table_a 
                          External table "public.table_a"
  Column   |       Type        | Modifiers | Storage  | Stats target | Description 
-----------+-------------------+-----------+----------+--------------+-------------
 date_inic | date              |           | plain    |              | 
 func_name | character varying |           | extended |              | 
 q_session | bigint            |           | plain    |              | 
Type: readable
Encoding: UTF8
Format type: text
Format options: delimiter '|' null 'N' escape ''
External options: {}
External location: gpfdist://teste:1010/tmp
Execute on: all segments

And from bash, you can just concat the strings together too.

loc="gpfdist://teste"
port="1010"
dir="tmp"
location="'""$loc"":""$port""/""$dir""'"

psql -f example.sql -v location="$location"