Syntax help please
| От | Ruben Gouveia |
|---|---|
| Тема | Syntax help please |
| Дата | |
| Msg-id | 51e507b0809041236s69ab9d6y5b3e0d6caeee8b8c@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: Syntax help please
|
| Список | pgsql-sql |
I can't for the life of me figure out what's wrong with this syntax. I get the following error when i try and create this function.
ERROR: syntax error at or near "$2" at character 15
QUERY: SELECT $1 $2 := $3 || $4 || $5
CONTEXT: SQL statement in PL/PgSQL function "fcn_gen_statement" near line 24
here's what i am trying to create:
CREATE OR REPLACE FUNCTION fcn_gen_statement(p_type varchar,
p_where varchar,
p_newonly numeric)
RETURNS varchar AS $$
DECLARE
c_select varchar(64) := 'select count(distinct m.id) ';
c_from varchar(64) := 'from job m ';
c_newonly_from varchar(128) := 'from (select id, min(date_created) as date_created '||
'from hr '||
'group_by id) m ';
v_from varchar(512);
v_where varchar(512);
v_stmt varchar(2048);
BEGIN
if p_newonly = 1 then
v_from := c_newonly_from;
else
v_from := c_from;
end if;
if upper(p_type) = 'NEW' then
v_stmt := c_select || v_from || p_where;
elsif upper(p_type) = 'OLD' then
v_from := c_from ;
v_where := p_where
v_stmt := c_select || v_from || v_where;
elsif upper(p_type) = 'LAST_JOB' then
v_from := v_from ||
', (select distinct job_id ' ||
'from job_log' ||
'where status = 10) d ';
v_where := p_where ||
'and m.id = d.job_id ';
v_stmt := c_select || v_from || v_where;
elsif upper(p_type) = 'NEW_JOB' then
v_from := v_from ||
', (select distinct job_id ' ||
'from job_log' ||
'where status = 12) d ';
v_where := p_where ||
'and m.id = d.job_id ';
v_stmt := c_select || v_from || v_where;
end if;
return (v_stmt);
END;
$$ LANGUAGE plpgsql;
ERROR: syntax error at or near "$2" at character 15
QUERY: SELECT $1 $2 := $3 || $4 || $5
CONTEXT: SQL statement in PL/PgSQL function "fcn_gen_statement" near line 24
here's what i am trying to create:
CREATE OR REPLACE FUNCTION fcn_gen_statement(p_type varchar,
p_where varchar,
p_newonly numeric)
RETURNS varchar AS $$
DECLARE
c_select varchar(64) := 'select count(distinct m.id) ';
c_from varchar(64) := 'from job m ';
c_newonly_from varchar(128) := 'from (select id, min(date_created) as date_created '||
'from hr '||
'group_by id) m ';
v_from varchar(512);
v_where varchar(512);
v_stmt varchar(2048);
BEGIN
if p_newonly = 1 then
v_from := c_newonly_from;
else
v_from := c_from;
end if;
if upper(p_type) = 'NEW' then
v_stmt := c_select || v_from || p_where;
elsif upper(p_type) = 'OLD' then
v_from := c_from ;
v_where := p_where
v_stmt := c_select || v_from || v_where;
elsif upper(p_type) = 'LAST_JOB' then
v_from := v_from ||
', (select distinct job_id ' ||
'from job_log' ||
'where status = 10) d ';
v_where := p_where ||
'and m.id = d.job_id ';
v_stmt := c_select || v_from || v_where;
elsif upper(p_type) = 'NEW_JOB' then
v_from := v_from ||
', (select distinct job_id ' ||
'from job_log' ||
'where status = 12) d ';
v_where := p_where ||
'and m.id = d.job_id ';
v_stmt := c_select || v_from || v_where;
end if;
return (v_stmt);
END;
$$ LANGUAGE plpgsql;
В списке pgsql-sql по дате отправления: