Function returns empty table

Question

I need to create function returning schedule of trains in different stations. I have something like that:

create or replace function f124(st varchar) returns table(odjazdy varchar, kierunek varchar, przewoznik varchar, peron varchar, tor int)
as
$$ declare s varchar; sta varchar:=st;
begin
drop table if exists f124p;
create table f124p(odjazdy varchar, kierunek varchar, przewoznik varchar, peron varchar, tor int);
select string_agg('insert into f124p select k."'||nr||'", ''stacja'', ''przewoznik'', t.peron, t.tor from gdymal_ic_kursy k inner join gdymal_ic_trasa t on k.stacja=t.stacja where t.stacja=''sta'';','') into s from generate_series(5110, 5118,2) as nr;
execute s;
return query select * from f124p;
end $$ language plpgsql;

The problem is that this function returns empty table. It shouldn't be like that beacuse there are information which must be in this table. I think there is a problem near k."'||nr||'" The insert should bring data from columns named "5110", "5112" as varchars. (In gdymal_ic_kursy there are columns "5110", "5112", ..., "5118"). Maybe this is a problem. Maybe You have any tips how should I repair this function?

Answer 1

I don't know what it means, but it works here:

---

\i tmp.sql

CREATE TABLE gdymal_ic_kursy
        ( stacja text
        , "5110" text
        , "5111" text
        , "5112" text
        , "5113" text
        , "5114" text
        , "5115" text
        , "5116" text
        , "5117" text
        , "5118" text
        );
INSERT INTO gdymal_ic_kursy VALUES ('sta', '1', '2', '3', '4', '5', '6', '7', '8');

CREATE TABLE gdymal_ic_trasa
        ( stacja text
        , peron text
        , tor integer
        );
INSERT INTO gdymal_ic_trasa(stacja,peron,tor) VALUES ('sta', 'one', 666 );

-- ------------------------------
create or replace function f124(st varchar)
    returns table(odjazdy varchar, kierunek varchar, przewoznik varchar, peron varchar, tor int)
as
$func$
declare s varchar; sta varchar := st;

begin

drop table if exists f124p;

create table f124p(odjazdy varchar, kierunek varchar, przewoznik varchar, peron varchar, tor int);

select string_agg('insert into f124p select k."'||nr||'", ''stacja'', ''przewoznik'', t.peron, t.tor
        from gdymal_ic_kursy k
        inner join gdymal_ic_trasa t on k.stacja=t.stacja
        where t.stacja=''sta'';','')
into s from generate_series(5110, 5118,2) as nr;

execute s;
return query select * from f124p;
end $func$ language plpgsql;

select *
FROM f124('OMG')
        ;

---

Result:

---

DROP SCHEMA
CREATE SCHEMA
SET
CREATE TABLE
INSERT 0 1
CREATE TABLE
INSERT 0 1
CREATE FUNCTION
NOTICE:  table "f124p" does not exist, skipping
 odjazdy | kierunek | przewoznik | peron | tor 
---------+----------+------------+-------+-----
 1       | stacja   | przewoznik | one   | 666
 3       | stacja   | przewoznik | one   | 666
 5       | stacja   | przewoznik | one   | 666
 7       | stacja   | przewoznik | one   | 666
         | stacja   | przewoznik | one   | 666
(5 rows)