Thread: quotes nightmare!

I am having trouble with my quotes in a function and think I have tried
every comb (obviously not) as it does not work:

I am trying to create a function within a function passing the second
function into the first as a parameter as follows:-

CREATE FUNCTION _test(text) RETURNS int AS '
BEGINEXECUTE ''CREATE FUNCTION _test'' || $1 || '' () RETURNS int AS ''BEGIN    RAISE EXCEPTION ''Help'';RETURN
2;END'';LANGUAGE ''plpgsql'' '';
 
RETURN 1;
END;'
LANGUAGE 'plpgsql';

SELECT _test('ing');

Could anyone tell me which bit is causing the problem as I am now completely
lost, the error message mentions is near LANGUAGE.

Many thanks

Graham.

On Fri, 22 Feb 2002, Graham Vickrage wrote:

> I am having trouble with my quotes in a function and think I have tried
> every comb (obviously not) as it does not work:
>
> I am trying to create a function within a function passing the second
> function into the first as a parameter as follows:-
>
> CREATE FUNCTION _test(text) RETURNS int AS '
> BEGIN
>     EXECUTE ''
>     CREATE FUNCTION _test'' || $1 || '' () RETURNS int AS ''
>     BEGIN
>         RAISE EXCEPTION ''Help'';
>     RETURN 2;
>     END''; LANGUAGE ''plpgsql'' '';
shouldn't that first semicolon be inside the '' with the end?

> RETURN 1;
> END;'
> LANGUAGE 'plpgsql';
>
> SELECT _test('ing');
>
> Could anyone tell me which bit is causing the problem as I am now completely
> lost, the error message mentions is near LANGUAGE.
>
> Many thanks
>
> Graham.
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

On Fri, Feb 22, 2002 at 01:18:51PM +0000, Graham Vickrage wrote:
> BEGIN
>     EXECUTE ''
>     CREATE FUNCTION _test'' || $1 || '' () RETURNS int AS ''

When you enter these '' you are ending the EXECUTE block. If you don't
want to end thit EXECUTE block, then you have to espace each of these
quotes.

> Could anyone tell me which bit is causing the problem as I am now completely
> lost, the error message mentions is near LANGUAGE.

You should look at:

http://www.postgresql.org/idocs/index.php?plpgsql-porting.html#PLPGSQL-QUOTING-TABLE
http://www.postgresql.org/idocs/index.php?plpgsql-porting.html#PLPGSQL-PORTING-NASTYQUOTE

-Roberto

-- 
+----| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |------+ Roberto Mello - Computer Science, USU -
http://www.brasileiro.net/      http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer    
 
Advisor: The guy who told you how to screw up