postgresql - Can't use an IF ... THEN ... ELSE inside my CASE statment SQL state: 42601 -

-

i building insert query on fly. here collecting column names. columns need scrambled, , need decrypted, scrambled , encrypted.

my idea check if column name in provided array, , if is, check see if value start 'pi2'. if so, apply necessary transformation decrypt, translate, encrypt. else translate value. here query: 

select case    when column_name = any(array['a', 'pi2_b', 'pi2_c', 'd', 'e'])       (        if (substring(column_name 0 4) = 'pi2')           'my_function.my_encrypt(translate(my_function.my_decrypt('||column_name ||'), '''|| var_scramble_key1 ||''','''|| var_scramble_key2 ||'''))';      else 'translate('||column_name||','''|| var_scramble_key1 ||''','''|| var_scramble_key2 ||''')';      end if; )     else column_name end information_schema.columns table_schema = 'my_temp' , table_name = 'table_a' order ordinal_position asc

running query gives me syntax error:

at or near 'then' sql state: 42601 character: 177.

my question why can't use if else inside case statement.

i have workaround:

select case    when column_name = any(array['a', 'pi2_b', 'pi2_c', 'd', 'e']) , substring(column_name 0 4) = 'pi2'           'my_function.my_encrypt(translate(my_function.my_decrypt('||column_name ||'), '''|| var_scramble_key1 ||''','''|| var_scramble_key2 ||'''))';      when column_name = any(array['a', 'pi2_b', 'pi2_c', 'd', 'e'])   'translate('||column_name||','''|| var_scramble_key1 ||''','''|| var_scramble_key2 ||''')';    else column_name end information_schema.columns table_schema = 'my_temp' , table_name = 'table_a' order ordinal_position asc

because if not sql key word.
key word if valid in pl/pgsql not within sql statement.
case happens valid in both (but there subtle syntax differences!)


Comments

Post a Comment

Popular posts from this blog

java - Custom OutputStreamAppender not run: LOGBACK: No context given for <MYAPPENDER> -

-
i wish write custom appender on basis of outputstreamappender . wrote following class package tests; import java.io.ioexception; import java.io.outputstream; import ch.qos.logback.core.outputstreamappender; public class myappender<e> extends outputstreamappender<e> { public myappender() { system.out.println("myappender created"); setoutputstream(new outputstream() { @override public void write(int b) throws ioexception { throw new unsupportedoperationexception(); } }); } } then wrote following runner: package tests; import org.slf4j.loggerfactory; import ch.qos.logback.classic.logger; public class runner { private static final logger log = (logger) loggerfactory.getlogger(runner.class); public static void main(string[] args) { //log.addappender(new myappender<iloggingevent>()); log.info("hello world"); } } finall...
Read more

java - UML - How would you draw a try catch in a sequence diagram? -

-
Image
is there standard way of drawing try catch sequence diagrams? i've made attempt based on this feel end result doesn't feel right. code diagram based around: public static void save () { try { filehandle filehandle = gdx.files.external(file); filehandle.writestring(boolean.tostring(constantshandler.soundenabled)+"\n", false); (int = 0; < 5; i++) { filehandle.writestring(integer.tostring(constantshandler.highscores[i])+"\n", true); } } catch (throwable e) { } } my attempt note: know still need add loop. that's ok. fragments meant show conditional control flow in sequence diagram. superstructures puts limitations on use of fragments (see pp. 467 of ss2.1.1). should use critical region try part above. , option catch . however, keep telling can take quite freedom in using uml. it's language , such changes in life time. long reader ge...
Read more

c++ - No viable overloaded operator for references a map -

-
i trying use map can make tag names reference number. when try , use it, in code error (6 in total every time reference map): src/main.cpp:25:45: error: no viable overloaded operator[] type 'std::map<std::string, std::string>' const char* idcs = node.child_value(tagmap[3]); this code: #include "pugi/pugixml.hpp" #include <iostream> #include <string> #include <map> int main() { pugi::xml_document doca, docb; std::map<std::string, pugi::xml_node> mapa, mapb; std::map<std::string, std::string> tagmap {{"1", "data"}, {"2", "entry"}, {"3", "id"}, {"4", "content"}}; if (!doca.load_file("a.xml") || !docb.load_file("b.xml")) { std::cout << "can't find input files"; return 1; } (auto& node: doca.child(tagmap[1]).children(tagmap[2])) { co...
Read more