[SQL] Syntax help please
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;
Re: [SQL] Syntax help please
You seem to be missing a ';' in this line: v_from := c_from ; v_where := p_where<--- missing ; here v_stmt := c_select || v_from || v_where; Regards, Fernando De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] En nombre de Ruben Gouveia Enviado el: Jueves, 04 de Septiembre de 2008 16:37 Para: pgsql-sql@postgresql.org Asunto: [SQL] Syntax help please 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; -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Syntax help please
I can't believe myself. It always comes down to some missing semicolon. Thanks for your help. I am embarrassed. On Thu, Sep 4, 2008 at 12:49 PM, Fernando Hevia <[EMAIL PROTECTED]>wrote: > You seem to be missing a ';' in this line: > >v_from := c_from ; >v_where := p_where<--- missing ; here > v_stmt := c_select || v_from || v_where; > > Regards, > Fernando > > > > > >De: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] En nombre de Ruben Gouveia >Enviado el: Jueves, 04 de Septiembre de 2008 16:37 >Para: pgsql-sql@postgresql.org >Asunto: [SQL] Syntax help please > > >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; > > > > > >
[SQL] Dynamic SQL Syntax help please
I created a simple function that i want to use another function (in the same schema) that will be used to create a sql statement. I get the following error when i try this: *ERROR: syntax error at or near "EXEC" at character 1 QUERY: EXEC SQL EXECUTE $1 INTO $2 CONTEXT: SQL statement in PL/PgSQL function "fcn_population" near line 10 ** Error ** ERROR: syntax error at or near "EXEC" SQL state: 42601 Context: SQL statement in PL/PgSQL function "fcn_population" near line 10* Here is my function: CREATE OR REPLACE FUNCTION fcn_population(p_date date, p_where varchar) RETURNS numeric AS $$ DECLARE v_total_pop numeric := 0; v_stmt varchar(1024); v_where varchar(256) := 'where m.date_created < '||p_date||' '||+ integer '1'||' '; BEGIN v_stmt := fcn_gen_statement(p_type, v_where, 0); EXEC SQL EXECUTE v_stmt INTO v_total_pop; return v_total_pop; END; $$ LANGUAGE plpgsql;
Re: [SQL] Dynamic SQL Syntax help please
On Thu, 4 Sep 2008 13:41:16 -0700 "Ruben Gouveia" <[EMAIL PROTECTED]> wrote: > I created a simple function that i want to use another function > (in the same schema) that will be used to create a sql statement. > I get the following error when i try this: > > *ERROR: syntax error at or near "EXEC" at character 1 > QUERY: EXEC SQL EXECUTE $1 INTO $2 > CONTEXT: SQL statement in PL/PgSQL function "fcn_population" near > line 10 > EXEC SQL EXECUTE v_stmt INTO v_total_pop; Could it be you're mixing MS SQL (Oracle?) syntax with pgplsql syntax EXECUTE v_stmt INTO v_total_pop; http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Dynamic SQL Syntax help please
thank you Sergio. I decided to use EXECUTE v_stmt into v_total_pop; On Thu, Sep 4, 2008 at 2:00 PM, Ivan Sergio Borgonovo <[EMAIL PROTECTED]>wrote: > On Thu, 4 Sep 2008 13:41:16 -0700 > "Ruben Gouveia" <[EMAIL PROTECTED]> wrote: > > > I created a simple function that i want to use another function > > (in the same schema) that will be used to create a sql statement. > > I get the following error when i try this: > > > > *ERROR: syntax error at or near "EXEC" at character 1 > > QUERY: EXEC SQL EXECUTE $1 INTO $2 > > CONTEXT: SQL statement in PL/PgSQL function "fcn_population" near > > line 10 > > > EXEC SQL EXECUTE v_stmt INTO v_total_pop; > > Could it be you're mixing MS SQL (Oracle?) syntax with pgplsql syntax > > EXECUTE v_stmt INTO v_total_pop; > > > http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN > > -- > Ivan Sergio Borgonovo > http://www.webthatworks.it > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
[SQL] Correct Insert SQL syntax?
Will this syntax work: fcn_stats are all in the same schema CREATE OR REPLACE FUNCTION insert_stats(p_date date) RETURNS void AS $$ BEGIN insert into stats ( date, stats1, stats2 ) (select p_date, fcn_stats1(p_date,'basic'), fcn_stats2(p_date,'basic',0) from dual ); END; $$ LANGUAGE 'plpgsql';
Re: [SQL] Correct Insert SQL syntax?
There is no internal dual table in PG unlike Ora:) If you need to invoke non-set-returning function simply execute: SELECT my_func(p1, p2...); -- Best regards, Yuri. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Correct Insert SQL syntax?
Thanks Yuri! On Thu, Sep 4, 2008 at 3:49 PM, Yura Gal <[EMAIL PROTECTED]> wrote: > There is no internal dual table in PG unlike Ora:) If you need to > invoke non-set-returning function simply execute: > > SELECT my_func(p1, p2...); > > -- > Best regards, Yuri. >
