PostgreSQL 9.3: missing FROM-clause entry for table

Question

I have a table with two columns.

Example:

create table t1
( 
  cola varchar,
  colb varchar
);

Now I want to insert the rows from function.

In the function: I want to use two parameters which is of type varchar to insert the values into the above table. I am passing the string to insert into the table.

I am passing two string of characters as a parameters to the function:

Parameters:

cola varchar = 'a,b,c,d';
colb varchar = 'e,f,g,h';

The above parameters have to insert into the table like this:

cola       colb
----------------
a           e
b           f
c           g
d           h

My try:

create or replace function fun_t1(cola varchar,colb varchar)
returns void as
$body$
Declare
    v_Count integer;
    v_i integer = 0;
    v_f1 text;
    v_cola varchar;
    v_colb varchar;
    v_query varchar;

Begin

    drop table if exists temp_table;

    create temp table temp_table
    (
        cola varchar,
        colb varchar
    );

    v_Count :=  length(cola) - length(replace(cola, ',', ''));

    raise info '%',v_Count;

    WHILE(v_i<=v_Count) LOOP

        INSERT INTO temp_table
        SELECT  LEFT(cola,CHARINDEX(',',cola||',',0)-1) 
        ,LEFT(colb,CHARINDEX(',',colb||',',0)-1);

        cola := overlay(cola placing '' from 1 for CHARINDEX(',',cola,0));

        colb := overlay(colb placing '' from 1 for CHARINDEX(',',colb,0));

        v_i := v_i + 1;

    END LOOP;

    for v_f1 IN select * from temp_table loop

        v_cola := v_f1.cola;  /* Error occurred here */
        v_colb := v_f1.colb;  

        v_query := 'INSERT INTO t1 values('''||v_cola||''','''||v_colb||''')';

        execute v_query;

    end loop;

end;

$body$

language plpgsql;

Note: In the function I have used temp_table that is according to the requirement which I am using for the other use also in the function which I have not display here.

Calling function:

SELECT fun_t1('a,b,c','d,e,f');

Getting an error:

missing FROM-clause entry for table "v_f1"

Does your items number for both columns is always same for example from the above we can say that its `4` for both columns — Vivek S., Mar 11 '15 at 05:11
@unique_id, Yup! It's always same. I may insert 5 for `cola` and 5 `colb`. — MAK, Mar 11 '15 at 05:13

Vivek S. · Accepted Answer · 2015-03-11T06:35:05.473

Try this way using split_part() : -

create or replace function ins_t1(vala varchar,valb varchar,row_cnt int) returns void as 
$$
BEGIN 
FOR i IN 1..row_cnt LOOP -- row_cnt is the number rows you need to insert (ex. 4 or 5 or whatever it is)
insert into t1 (cola,colb) 
values (
        (select split_part(vala,',',i))
       ,(select split_part(valb,',',i))
       );
END LOOP;
END;
$$
language plpgsql

function call :select ins_t1('a,b,c,d','e,f,g,h',4)

As mike-sherrill-cat-recall said in his answer by using regexp_split_to_table

create or replace function fn_t1(vala varchar,valb varchar) returns void
as
$$
insert into t1 (cola, colb)
select col1, col2 from (select 
    trim(regexp_split_to_table(vala, ','))  col1,   
    trim(regexp_split_to_table(valb, ','))  col2)t;
$$
language sql

function call :select fn_t1('a,b,c,d','e,f,g,h')

Can you please help me for this: http://stackoverflow.com/questions/28855405/postgresql-9-3-dynamic-pivot-table-for-huge-records — MAK, Mar 11 '15 at 06:41

Mike Sherrill 'Cat Recall' · Answer 2 · 2015-03-11T06:27:47.330

If there's no compelling reason to use a function for this, you can just split the text using a regular expression. Here I've expressed your arguments as a common table expression, but that's just for convenience.

with data (col1, col2) as (
  select 'a, b, c, d'::text, 'e, f, g, h'::text
)
select 
    trim(regexp_split_to_table(col1, ',')) as col_a,   
    trim(regexp_split_to_table(col2, ',')) as col_b
from data;

col_a  col_b
--
a      e
b      f
c      g
d      h

If there is a compelling reason to use a function, just wrap a function definition around that SELECT statement.

create function strings_to_table(varchar, varchar) 
returns table (col_a varchar, col_b varchar)
as 
    'select trim(regexp_split_to_table($1, '','')),
            trim(regexp_split_to_table($2, '',''));'
language sql
stable
returns null on null input;

select * from strings_to_table('a,b,c,d', 'e,f, g, h');

col_a  col_b
--
a      e
b      f
c      g
d      h

My personal preference is usually to build functions like this to return tables rather than inserting into tables. To insert, I'd usually write a SQL statement like this.

insert into foo (col_a, col_b)
select col_a, col_b from strings_to_table('a,b,c,d', 'e,f,g,h');

Just awesome. The function `strings_to_table` really a good idea. Thank you sooooo much for sharing. — MAK, Mar 11 '15 at 06:34
Can you please help me for this: http://stackoverflow.com/questions/28855405/postgresql-9-3-dynamic-pivot-table-for-huge-records — MAK, Mar 11 '15 at 06:36

score 1 · Answer 3 · answered Mar 11 '15 at 07:34

The simpest way is using plpython for this.

create or replace function fill_t1(cola varchar, colb varchar) returns void as $$
for r in zip(cola.split(','), colb.split(',')):
  plpy.execute(plpy.prepare('insert into t1(cola, colb) values ($1, $2)', ['varchar', 'varchar']), [r[0], r[1]])
$$ language plpythonu;

The result:

# create table t1 (cola varchar, colb varchar);
CREATE TABLE
# select fill_t1('1,2,3', '4,5,6');
 fill_t1 
---------

(1 row)

# select * from t1;
 cola | colb 
------+------
 1    | 4
 2    | 5
 3    | 6
(3 rows)

You can read about Python zip function here: https://docs.python.org/2/library/functions.html#zip

PostgreSQL 9.3: missing FROM-clause entry for table

My try:

3 Answers3