select into not supported within stored procedures?

#1

Hi, I am trying to execute a select into statement inside a sp, if I
test it as plain sql command it works well, when I try to create the sp
with this command inside it gives me an error, is there a workaround?

#2

Coniglio Sgabbiato <nobody (AT) nowhere (DOT) it> wrote:

Quote:

Hi, I am trying to execute a select into statement inside a sp, if I
test it as plain sql command it works well, when I try to create the sp
with this command inside it gives me an error, is there a workaround?

Works for me, as a plain select into and as a execute 'sql' into:

test=# create or replace function select_into_test() returns int as $$declare r int; begin select into r 1; return r; end; $$language plpgsql;
CREATE FUNCTION
Zeit: 228,802 ms
test=*# select * from select_into_test();
select_into_test
------------------
1
(1 Zeile)

Zeit: 0,984 ms
test=*# rollback;
ROLLBACK
Zeit: 0,246 ms
test=# create or replace function select_into_test() returns int as $$declare r int; begin execute 'select 1' into r; return r; end; $$language plpgsql;
CREATE FUNCTION
Zeit: 0,397 ms
test=*# select * from select_into_test();
select_into_test
------------------
1
(1 Zeile)

Please, show us your function and tell us your pg-version.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

#3

Coniglio Sgabbiato <nobody (AT) nowhere (DOT) it> wrote:

Quote:

Hi, I am trying to execute a select into statement inside a sp, if I
test it as plain sql command it works well, when I try to create the sp
with this command inside it gives me an error, is there a workaround?

Works for me, as a plain select into and as a execute 'sql' into:

test=# create or replace function select_into_test() returns int as $$declare r int; begin select into r 1; return r; end; $$language plpgsql;
CREATE FUNCTION
Zeit: 228,802 ms
test=*# select * from select_into_test();
select_into_test
------------------
1
(1 Zeile)

Zeit: 0,984 ms
test=*# rollback;
ROLLBACK
Zeit: 0,246 ms
test=# create or replace function select_into_test() returns int as $$declare r int; begin execute 'select 1' into r; return r; end; $$language plpgsql;
CREATE FUNCTION
Zeit: 0,397 ms
test=*# select * from select_into_test();
select_into_test
------------------
1
(1 Zeile)

Please, show us your function and tell us your pg-version.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

#4

Coniglio Sgabbiato <nobody (AT) nowhere (DOT) it> wrote:

Quote:

Hi, I am trying to execute a select into statement inside a sp, if I
test it as plain sql command it works well, when I try to create the sp
with this command inside it gives me an error, is there a workaround?

Works for me, as a plain select into and as a execute 'sql' into:

test=# create or replace function select_into_test() returns int as $$declare r int; begin select into r 1; return r; end; $$language plpgsql;
CREATE FUNCTION
Zeit: 228,802 ms
test=*# select * from select_into_test();
select_into_test
------------------
1
(1 Zeile)

Zeit: 0,984 ms
test=*# rollback;
ROLLBACK
Zeit: 0,246 ms
test=# create or replace function select_into_test() returns int as $$declare r int; begin execute 'select 1' into r; return r; end; $$language plpgsql;
CREATE FUNCTION
Zeit: 0,397 ms
test=*# select * from select_into_test();
select_into_test
------------------
1
(1 Zeile)

Please, show us your function and tell us your pg-version.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

#5

Coniglio Sgabbiato <nobody (AT) nowhere (DOT) it> wrote:

Quote:

Hi, I am trying to execute a select into statement inside a sp, if I
test it as plain sql command it works well, when I try to create the sp
with this command inside it gives me an error, is there a workaround?

Works for me, as a plain select into and as a execute 'sql' into:

test=# create or replace function select_into_test() returns int as $$declare r int; begin select into r 1; return r; end; $$language plpgsql;
CREATE FUNCTION
Zeit: 228,802 ms
test=*# select * from select_into_test();
select_into_test
------------------
1
(1 Zeile)

Zeit: 0,984 ms
test=*# rollback;
ROLLBACK
Zeit: 0,246 ms
test=# create or replace function select_into_test() returns int as $$declare r int; begin execute 'select 1' into r; return r; end; $$language plpgsql;
CREATE FUNCTION
Zeit: 0,397 ms
test=*# select * from select_into_test();
select_into_test
------------------
1
(1 Zeile)

Please, show us your function and tell us your pg-version.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

#6

Coniglio Sgabbiato <nobody (AT) nowhere (DOT) it> wrote:

Quote:

Hi, I am trying to execute a select into statement inside a sp, if I
test it as plain sql command it works well, when I try to create the sp
with this command inside it gives me an error, is there a workaround?

Works for me, as a plain select into and as a execute 'sql' into:

test=# create or replace function select_into_test() returns int as $$declare r int; begin select into r 1; return r; end; $$language plpgsql;
CREATE FUNCTION
Zeit: 228,802 ms
test=*# select * from select_into_test();
select_into_test
------------------
1
(1 Zeile)

Zeit: 0,984 ms
test=*# rollback;
ROLLBACK
Zeit: 0,246 ms
test=# create or replace function select_into_test() returns int as $$declare r int; begin execute 'select 1' into r; return r; end; $$language plpgsql;
CREATE FUNCTION
Zeit: 0,397 ms
test=*# select * from select_into_test();
select_into_test
------------------
1
(1 Zeile)

Please, show us your function and tell us your pg-version.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

#7

Coniglio Sgabbiato <nobody (AT) nowhere (DOT) it> wrote:

Quote:

Hi, I am trying to execute a select into statement inside a sp, if I
test it as plain sql command it works well, when I try to create the sp
with this command inside it gives me an error, is there a workaround?

Works for me, as a plain select into and as a execute 'sql' into:

test=# create or replace function select_into_test() returns int as $$declare r int; begin select into r 1; return r; end; $$language plpgsql;
CREATE FUNCTION
Zeit: 228,802 ms
test=*# select * from select_into_test();
select_into_test
------------------
1
(1 Zeile)

Zeit: 0,984 ms
test=*# rollback;
ROLLBACK
Zeit: 0,246 ms
test=# create or replace function select_into_test() returns int as $$declare r int; begin execute 'select 1' into r; return r; end; $$language plpgsql;
CREATE FUNCTION
Zeit: 0,397 ms
test=*# select * from select_into_test();
select_into_test
------------------
1
(1 Zeile)

Please, show us your function and tell us your pg-version.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

#8

Coniglio Sgabbiato <nobody (AT) nowhere (DOT) it> wrote:

Quote:

Hi, I am trying to execute a select into statement inside a sp, if I
test it as plain sql command it works well, when I try to create the sp
with this command inside it gives me an error, is there a workaround?

Works for me, as a plain select into and as a execute 'sql' into:

test=# create or replace function select_into_test() returns int as $$declare r int; begin select into r 1; return r; end; $$language plpgsql;
CREATE FUNCTION
Zeit: 228,802 ms
test=*# select * from select_into_test();
select_into_test
------------------
1
(1 Zeile)

Zeit: 0,984 ms
test=*# rollback;
ROLLBACK
Zeit: 0,246 ms
test=# create or replace function select_into_test() returns int as $$declare r int; begin execute 'select 1' into r; return r; end; $$language plpgsql;
CREATE FUNCTION
Zeit: 0,397 ms
test=*# select * from select_into_test();
select_into_test
------------------
1
(1 Zeile)

Please, show us your function and tell us your pg-version.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

#9

Coniglio Sgabbiato <nobody (AT) nowhere (DOT) it> wrote:

Quote:

Hi, I am trying to execute a select into statement inside a sp, if I
test it as plain sql command it works well, when I try to create the sp
with this command inside it gives me an error, is there a workaround?

Works for me, as a plain select into and as a execute 'sql' into:

test=# create or replace function select_into_test() returns int as $$declare r int; begin select into r 1; return r; end; $$language plpgsql;
CREATE FUNCTION
Zeit: 228,802 ms
test=*# select * from select_into_test();
select_into_test
------------------
1
(1 Zeile)

Zeit: 0,984 ms
test=*# rollback;
ROLLBACK
Zeit: 0,246 ms
test=# create or replace function select_into_test() returns int as $$declare r int; begin execute 'select 1' into r; return r; end; $$language plpgsql;
CREATE FUNCTION
Zeit: 0,397 ms
test=*# select * from select_into_test();
select_into_test
------------------
1
(1 Zeile)

Please, show us your function and tell us your pg-version.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

#10

Andreas Kretschmer ha scritto:
[CUT]

Quote:

Please, show us your function and tell us your pg-version.

I think the problem it is that I am trying to create a table instead of
getting a value:

create or replace function test() returns void as $c$
begin
select * into tmp.newtable from (select field1 as alias1,
sum(cast(field2 as int)) as alias2 from tmp.oldtable group by field1
having field1<something) as t;
end;
$c$ language plpgsql;

ERROR: syntax error at "tmp.newtemptable"
DETAIL: Expected record variable, row variable, or list of scalar
variables following INTO.
CONTEXT: compile of PL/pgSQL function "test" near line 2

# /usr/lib/postgresql/8.3/bin/postgres --version
postgres (PostgreSQL) 8.3.5