Re: [GENERAL] variable value in array_to_string

[David G. Johnston]

For what its worth:

DO $$
DECLARE
vresult text;
begin
EXECUTE $qry$
  WITH cols (c) AS ( VALUES ('col1'),('col2') )
  SELECT string_agg('a.' || cols.c, ',')
  FROM cols
  WHERE 'foo' = $1
$qry$
USING 'foo'
INTO vresult;
RAISE NOTICE '%', vresult;
END;
$$;

I still haven't actually figured out what your question is which is maybe
why I'm coming across as patronizing.  And while I get that what you wrote
is technically correct its using capabilities that have since be superseded
by more readable and less error-prone facilities.

The docs do explain this material in terms of technical capabilities.  For
better and worse it doesn't always go to great lengths (or keeps up with
changing times) to describe best practices or how multiple pieces fit
together.

​I'm also thinking that your original question is more accurately worded:
 Is there anyway I can [use an expression] in the array_to_string function

I'm not nit-picking here - the word variable when you writing a pl/pgsql
language function has a very specific meaning which is not <'a.' ||
column_name>

In both cases the answer is a simple yes.  Sorry for causing pain by
offering up more information than requested.  Its bad habit of mine -
trying to provide related information when presented with a question.

David J.

Re: [GENERAL] variable value in array_to_string

[Armand Pirvu (home)]

1 - I added some new notes.
2 - That code I know and works fine. I used it in  slightly different contexts 
in other projects

Here it is a case . 

A table with two columns , data type is irrelevant


I have a need in which I may get a.col1,a.col2 and other variations

Point is yes I could build any variation re-running a slightly different way 
the query in the catalogs

But why hit 3 or 4 times when I could manipulate an array  like below ? This 
code just came in my mind (and works)


CREATE OR REPLACE FUNCTION test1 () RETURNS void AS $$
DECLARE
var1 text[];
var2 text;
var3 text;
var4 text;
begin
execute 
'select ARRAY( select column_name::text from information_schema.columns where 
table_name='
||
quote_literal('foo')
||')'  into var1;
raise notice '%', var1;
select array_to_string(array(select  'a.'||unnest(var1)),',') into var4;
raise notice '%', var4;
END;
$$ LANGUAGE plpgsql ;


levregdb1=# select test1(); 

 NOTICE:  
{col1,col2}
NOTICE:  a.col1,a.col2

Note the a. being prepended to each of the array’s elements


3 - I appreciate the help. Keep in mind I am not a developer and this is a new 
venture for me.  On the same token  I am not an ignorant either . What 
irritates me is the TRUE lack in this case of explanations in the docs. I know 
another DBMS just  as bad in the documentation area (worse in fact)


I turned to the community. And yes I know how to make cases since I worked 
support.

Frankly I am turning off since I sure as heck don’t like being patronized. Did 
not accept this (and never will) from my own mom let alone somebody else

My apologies for asking help. Will try not to do it again


Thanks and laters



On Nov 21, 2016, at 7:12 PM, David G. Johnston  
wrote:

> Please don't top-post - it makes following the thread a lot harder.
> 
> On Mon, Nov 21, 2016 at 4:15 PM, Armand Pirvu (home)  
> wrote:
> Played with unnest  but not much luck
> 
> If you want help you will need to show your work - ideally with examples that 
> can execute with meaningful data on an empty database.
> 
> The follow gives the expected results:
> 
> DO $$
> DECLARE
> foo text[];
> foo1 text;
> delim text := ',';
> begin
> foo := ARRAY['one','two']::text[];
> foo1 := array_to_string(foo, delim);
> RAISE NOTICE '%', foo1;
> END;
> $$;
> 
> NOTICE: one,two
> 
> David J.
> 

Re: [GENERAL] variable value in array_to_string

[David G. Johnston]

Please don't top-post - it makes following the thread a lot harder.

On Mon, Nov 21, 2016 at 4:15 PM, Armand Pirvu (home)  wrote:

> Played with unnest  but not much luck
>

If you want help you will need to show your work - ideally with examples
that can execute with meaningful data on an empty database.

The follow gives the expected results:

DO $$
DECLARE
foo text[];
foo1 text;
delim text := ',';
begin
foo := ARRAY['one','two']::text[];
foo1 := array_to_string(foo, delim);
RAISE NOTICE '%', foo1;
END;
$$;

NOTICE: one,two

David J.

Re: [GENERAL] variable value in array_to_string

[Armand Pirvu (home)]

Played with unnest  but not much luck


NOTICE:  {item_id,show_id}
NOTICE:  item_id


It takes only the first array element in consideration


Ug

On Nov 21, 2016, at 5:02 PM, Armand Pirvu (home)  wrote:

> My bad on the back tick. No idea why it turned that way
> 
> OK got that David. 
> 
> The idea is that I have the pk columns in an array which I would like to 
> manipulate from the array itself rather than running same query variations 
> multiple times
> 
> For example I get in foo
> 
> {item_id,show_id}
> 
> And from here on I was looking to get say
> 
> a.item_id , a.show_id 
> 
> and all sort of variations
> 
> Thanks for help
> 
> 
> 
> 
> On Nov 21, 2016, at 4:26 PM, David G. Johnston  
> wrote:
> 
>> On Mon, Nov 21, 2016 at 3:09 PM, Armand Pirvu (home) 
>>  wrote:
>> Hi
>> 
>> Is there anyway I can pass a variable in the array_to_string function ?
>> 
>> 
>> ​Yes, just like you can pass variables to any other function.​..
>> 
>> 
>> CREATE OR REPLACE FUNCTION test1 () RETURNS void AS $$
>> ​​DECLARE
>> foo text;
>> foo1 text;
>> begin
>> execute
>> 'select ARRAY( SELECT d.COLUMN_NAME::text  from 
>> information_schema.constraint_table_usage c, 
>> information_schema.key_column_usage d '
>> ||
>> 'where c.constraint_name=d.constraint_name and c.table_name=d.table_name and 
>> c.table_schema='
>> ||
>> quote_literal(‘myschema')
>> ||
>> ' and c.table_schema=d.table_schema and c.constraint_name like '
>> ||
>> quote_literal('%_pkey%')
>> ||
>> ') ' into foo;
>> raise notice '%', foo;
>> foo1 :=ARRAY_TO_STRING(foo, ', ');
>> raise notice '%', foo1;
>> END;
>> $$ LANGUAGE plpgsql ;
>> 
>> 
>> The idea is that I want to slice and dice and not run the query x amount of 
>> times
>> 
>> 
>> I would suggest you learn to use the "format()" function, EXECUTE USING,​ 
>> and dollar quoting.
>> 
>> 
>> So, I copy-pasted your example and placed it into a DO block
>> 
>> The character before "myschema" came out as a back-tick instead of a 
>> single-quote.
>> 
>> array_to_string has the signature (text[], text) but you've defined foo as 
>> being just text.  Changing that results in a query that executes - though 
>> given my system doesn't have conforming data I get no results.
>> 
>> David J.
> 

Re: [GENERAL] variable value in array_to_string

[Armand Pirvu (home)]

My bad on the back tick. No idea why it turned that way

OK got that David. 

The idea is that I have the pk columns in an array which I would like to 
manipulate from the array itself rather than running same query variations 
multiple times

For example I get in foo

{item_id,show_id}

And from here on I was looking to get say

a.item_id , a.show_id 

and all sort of variations

Thanks for help




On Nov 21, 2016, at 4:26 PM, David G. Johnston  
wrote:

> On Mon, Nov 21, 2016 at 3:09 PM, Armand Pirvu (home)  
> wrote:
> Hi
> 
> Is there anyway I can pass a variable in the array_to_string function ?
> 
> 
> ​Yes, just like you can pass variables to any other function.​..
> 
> 
> CREATE OR REPLACE FUNCTION test1 () RETURNS void AS $$
> ​​DECLARE
> foo text;
> foo1 text;
> begin
> execute
> 'select ARRAY( SELECT d.COLUMN_NAME::text  from 
> information_schema.constraint_table_usage c, 
> information_schema.key_column_usage d '
> ||
> 'where c.constraint_name=d.constraint_name and c.table_name=d.table_name and 
> c.table_schema='
> ||
> quote_literal(‘myschema')
> ||
> ' and c.table_schema=d.table_schema and c.constraint_name like '
> ||
> quote_literal('%_pkey%')
> ||
> ') ' into foo;
> raise notice '%', foo;
> foo1 :=ARRAY_TO_STRING(foo, ', ');
> raise notice '%', foo1;
> END;
> $$ LANGUAGE plpgsql ;
> 
> 
> The idea is that I want to slice and dice and not run the query x amount of 
> times
> 
> 
> I would suggest you learn to use the "format()" function, EXECUTE USING,​ and 
> dollar quoting.
> 
> 
> So, I copy-pasted your example and placed it into a DO block
> 
> The character before "myschema" came out as a back-tick instead of a 
> single-quote.
> 
> array_to_string has the signature (text[], text) but you've defined foo as 
> being just text.  Changing that results in a query that executes - though 
> given my system doesn't have conforming data I get no results.
> 
> David J.

Re: [GENERAL] variable value in array_to_string

[David G. Johnston]

On Mon, Nov 21, 2016 at 3:09 PM, Armand Pirvu (home)  wrote:

> Hi
>
> Is there anyway I can pass a variable in the array_to_string function ?
>
>
​Yes, just like you can pass variables to any other function.​..


> CREATE OR REPLACE FUNCTION test1 () RETURNS void AS $$
> ​​
> DECLARE
> foo text;
> foo1 text;
> begin
> execute
> 'select ARRAY( SELECT d.COLUMN_NAME::text  from
> information_schema.constraint_table_usage c,
> information_schema.key_column_usage d '
> ||
> 'where c.constraint_name=d.constraint_name and c.table_name=d.table_name
> and c.table_schema='
> ||
> quote_literal(‘myschema')
> ||
> ' and c.table_schema=d.table_schema and c.constraint_name like '
> ||
> quote_literal('%_pkey%')
> ||
> ') ' into foo;
> raise notice '%', foo;
> foo1 :=ARRAY_TO_STRING(foo, ', ');
> raise notice '%', foo1;
> END;
> $$ LANGUAGE plpgsql ;
>
>
> The idea is that I want to slice and dice and not run the query x amount
> of times
>
>
I would suggest you learn to use the "format()" function, EXECUTE USING,​
and dollar quoting.


So, I copy-pasted your example and placed it into a DO block

The character before "myschema" came out as a back-tick instead of a
single-quote.

array_to_string has the signature (text[], text) but you've defined foo as
being just text.  Changing that results in a query that executes - though
given my system doesn't have conforming data I get no results.

David J.

[GENERAL] variable value in array_to_string

[Armand Pirvu (home)]

Hi

Is there anyway I can pass a variable in the array_to_string function ?




CREATE OR REPLACE FUNCTION test1 () RETURNS void AS $$
DECLARE
foo text;
foo1 text;
begin
execute 
'select ARRAY( SELECT d.COLUMN_NAME::text  from 
information_schema.constraint_table_usage c, 
information_schema.key_column_usage d '
||
'where c.constraint_name=d.constraint_name and c.table_name=d.table_name and 
c.table_schema='
||
quote_literal(‘myschema')
||
' and c.table_schema=d.table_schema and c.constraint_name like '
||
quote_literal('%_pkey%')
||
') ' into foo;
raise notice '%', foo;
foo1 :=ARRAY_TO_STRING(foo, ', ');
raise notice '%', foo1;
END;
$$ LANGUAGE plpgsql ;


The idea is that I want to slice and dice and not run the query x amount of 
times



Thank you

Armand

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general